Genarate Months Column in SQL

Question

I am using sql compact database SDF and I have table Sales below is structure

[SALES_ID] int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
[Sales_Date] datetime, 
[Sales_Time] nvarchar(50), 
[User_ID] bigint NOT NULL, 
[customer_id] bigint NOT NULL, 
[Payment] float

please help me to write an sql query that displays the total sales by month for each customer ID

Customer_ID | January | February| March | April | May |June | July |....... |December |

1 25000 24000 28000 30000 10000 40000

2 35000 42000 10000 55000 3000 27500

.....

thanks in advance

Answer 1

SELECT Customer_id, 
     SUM(CASE WHEN Month(Sales_date) = 1 THEN Payment END AS January,
     SUM(CASE WHEN Month(Sales_date) = 2 THEN Payment END AS February,
    ...
FROM tbl
GROUP BY Customer_id

This query runs on "big" SQL Server. I have not worked with SQL Compact Edition, so I don't know if the Month function is there.

Answer 2

Here is a list of option to transform rows to columns (using Erland's term, this is for "big" SQL Server)

(1) Using PIVOT

(2) Using multiple self-joins (one for each month) -don't try this as this is awful solution

(3) Using CROSS APPLY - my prefer option many times

SELECT Stage.id,v.idd, v.colc
FROM (VALUES ('1', 1, 2, 3),('2', 11, 22, 33)) AS Stage(id,col1,col2,col3) -- this just demonstrate the table which you want to use
CROSS APPLY (VALUES ('col1', col1),('col2', col2),('col3', col3)) AS v(idd,colc)
GO

(4) Using an aggregate function - this is what Erland provided

Answer 3

Hi @Devos2022
You can use Pivot like this:

SELECT * FROM   
     (SELECT customer_id, DATENAME(MONTH, Sales_Date) AS MName, Payment  
      FROM Sales   
	  WHERE Sales_Date BETWEEN 'Start_date' AND 'End_date') S  
PIVOT(SUM(Payment) FOR MName IN ([January],[February],[],....)) P  

If SQL Compact doesn't support PIVOT, you can also try this:

SELECT customer_id,  
SUM(CASE DATENAME(MONTH, Sales_Date) WHEN 'January' THEN Payment ELSE 0 END) AS January,  
SUM(CASE DATENAME(MONTH, Sales_Date) WHEN 'February' THEN Payment ELSE 0 END) AS February,  
...  
...  
FROM Sales   
WHERE Sales_Date BETWEEN 'Start_date' AND 'End_date'  
GROUP BY customer_id  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.