multiple column pivot in sql server

Question

I have some issue about how to make query result like

this is what i've tried,, but not exactly same like what i mean

CREATE TABLE #dta  
(  
 Data [nvarchar](max),  
 Date [varchar] (12) ,  
 GR [int]  ,
 Refund [int]  ,
 Sales [int]  ,
)  

 INSERT INTO #dta  
 SELECT 'asd',1,10,0,0 UNION ALL  
 SELECT 'asd',2,0,0,4 UNION ALL  
 SELECT 'asd',3,4,1,1 UNION ALL  
 SELECT 'qwe',1,2,0,0 UNION ALL  
 SELECT 'qwe',3,0,0,1 UNION ALL  
 SELECT 'zxc',1,0,0,5 UNION ALL  
 SELECT 'zxc',2,4,0,1 UNION ALL  
 SELECT 'zxc',3,0,1,5 


--Only for sales
SELECT data, [1],[2],[3] FROM   
(SELECT data, [date] , Sales FROM #dta )Tab1  
PIVOT  
(  
SUM(Sales) FOR [date] IN ([1],[2],[3])) AS Tab2  
ORDER BY Tab2.Data

You've told us that you have an issue, but not what the issue is. What's the issue, and what have you tried so far to solve it? It's worth reading [ask] and providing a [mcve] if you can. — Diado, Jun 24 '19 at 09:08
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server), [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server), [In Sql Server how to Pivot for multiple columns](https://stackoverflow.com/questions/38067490/in-sql-server-how-to-pivot-for-multiple-columns), etc, etc? — Thom A, Jun 24 '19 at 09:10
@Diado i've updated my question and i add also what i've tried — s-dept, Jun 24 '19 at 10:12

score 0 · Answer 1 · answered Jun 24 '19 at 10:41

You can use conditional aggregation and try something along this code:

 SELECT t.[Data]
       ,MAX(CASE WHEN t.[Date]=1 THEN t.Sales END) AS [Sales_Date_1]
       ,MAX(CASE WHEN t.[Date]=1 THEN t.Refund END) AS [Refund_Date_1]
       ,MAX(CASE WHEN t.[Date]=1 THEN t.GR END) AS [GR_Date_1]
       ,MAX(CASE WHEN t.[Date]=2 THEN t.Sales END) AS [Sales_Date_2]
       ,MAX(CASE WHEN t.[Date]=2 THEN t.Refund END) AS [Refund_Date_2]
       ,MAX(CASE WHEN t.[Date]=2 THEN t.GR END) AS [GR_Date_2]
       ,MAX(CASE WHEN t.[Date]=3 THEN t.Sales END) AS [Sales_Date_3]
       ,MAX(CASE WHEN t.[Date]=3 THEN t.Refund END) AS [Refund_Date_3]
       ,MAX(CASE WHEN t.[Date]=3 THEN t.GR END) AS [GR_Date_3]
 FROM #dta t
 GROUP BY t.[Data];

If the count of t.[Date] (1 to 3 in your example data) is not limited to a known maximum, you'd have to create the statement dyanmically.

If your t.[Date] column is a date value actually and you want your output columns named after some date values (unpredictable column alias) within your set, you must create this dynamically too.

multiple column pivot in sql server

1 Answers1