CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Tamil
Tamil

Reputation: 77

Converting rows to columns using multiple pivot in sql

I have written a query to convert rows into columns using multiple pivot functions with respect to months 4, 5 & 6. I did succeed in converting the rows into columns. Below is the query:

(SELECT [team], 
Count_Of_OrderId,
    Count_Of_OId,
            Avg_a,
            [Count_of_u]  ,
    convert(varchar(max),[month_from_Date])+'_COID'  as 
month_from_Date_COAID, 
    convert(varchar(max),[month_from_Date]) + '_COID' as 
 month_from_Date_CODID,
    convert(varchar(max),[month_from_Date])+'_Avg_a'  as 
month_from_Date_Avg_a, 
    convert(varchar(max),[month_from_Date])+'_Count_of_u'  as 
month_from_Date_Count_of_u

FROM [MyTable]) AS S  
PIVOT  
(  
    MAX(Count_Of_OrderId,)
    FOR [month_from_Date_COAID] IN ([4_COID], [5_COID], [6_COID])  
)   AS PivotTable1

PIVOT  
(  
    MAX(Count_Of_OId)
    FOR [month_from_Date_CODID] IN ([4_COID], [5_COID], [6_COID])  
)   AS PivotTable2

PIVOT  
(  
    MAX(Avg_a)
    FOR [month_from_Date_Avg_a] IN ([4_Avg_a], [5_Avg_a], [6_Avg_a])  
)   AS PivotTable3
PIVOT  
(  
    MAX(Count_of_users)
    FOR [month_from_Date_Count_of_u] IN ([4_Count_of_u], [5_Count_of_u], 
[6_Count_of_u])  
)   AS PivotTable4

So the output was: 

+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
|  Team  | COAID_4 | COAID_5 | COAID_6 | CODID_4 | CODID_5 | CODID_6 | Avg_a_4 | Avg_a_5 | Avg_a_6 | Count_of_u_4 | Count_of_u_5 | Count_of_u_6 |
+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
| Team A | NULL    | NULL    | 17      | NULL    | NULL    | 15      | NULL    | NULL    | 1.13    | NULL         | NULL         | 7            |
| Team A | NULL    | 14      | NULL    | NULL    | 14      | NULL    | NULL    | 1       | NULL    | NULL         | 6            | NULL         |
| Team A | 9       | NULL    | NULL    | 7       | NULL    | NULL    | 1.29    | NULL    | NULL    | 5            | NULL         | NULL         |
| Team B | NULL    | NULL    | 12159   | NULL    | NULL    | 6482    | NULL    | NULL    | 1.88    | NULL         | NULL         | 40           |
| Team B | NULL    | 14287   | NULL    | NULL    | 6525    | NULL    | NULL    | 2.19    | NULL    | NULL         | 39           | NULL         |
| Team B | 15822   | NULL    | NULL    | 7117    | NULL    | NULL    | 2.22    | NULL    | NULL    | 40           | NULL         | NULL         |
| Team C | NULL    | NULL    | 293     | NULL    | NULL    | 174     | NULL    | NULL    | 1.68    | NULL         | NULL         | 6            |
| Team C | NULL    | 318     | NULL    | NULL    | 221     | NULL    | NULL    | 1.44    | NULL    | NULL         | 6            | NULL         |
| Team C | 312     | NULL    | NULL    | 183     | NULL    | NULL    | 1.7     | NULL    | NULL    | 6            | NULL         | NULL         |
+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+

Here the the team has been split as 3 rows for 4th, 5th and 6th month. I would like to get the o/p as:

+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
|  Team  | COAID_4 | COAID_5 | COAID_6 | CODID_4 | CODID_5 | CODID_6 | Avg_a_4 | Avg_a_5 | Avg_a_6 | Count_of_u_4 | Count_of_u_5 | Count_of_u_6 |
+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
| Team A |       9 |      14 |      17 |       7 |      14 |      15 |    1.29 |       1 |    1.13 |            5 |            6 |            7 |
| Team B |   15822 |   14287 |   12159 |    7117 |    6525 |    6482 |    2.22 |    2.19 |    1.88 |           40 |           39 |           40 |
| Team C |     312 |     318 |     293 |     183 |     221 |     174 |     1.7 |    1.44 |    1.68 |            6 |            6 |            6 |
+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+

I am not sure, whats the mistake in my code.

Upvotes: 2

Views: 56

Answers (1)

D-Shih
D-Shih

Reputation: 46239

A simple way you can use MAX 

SELECT Team,
       MAX(COAID_4),
       MAX(COAID_5),
       MAX(COAID_6),
       ....
FROM T
GROUP BY Team

T is your current query result SQL.

But I think you are looking for condition aggregate function to make the pivot.

SELECT 
      [team], 
      MAX(CASE WHEN month_from_Date = 4 THEN Count_Of_OrderId END) '4_COID',
      MAX(CASE WHEN month_from_Date = 5 THEN Count_Of_OrderId END) '5_COID',
      MAX(CASE WHEN month_from_Date = 6 THEN Count_Of_OrderId END) '6_COID',
      MAX(CASE WHEN month_from_Date = 4 THEN Count_Of_OId END) '4_COID',
      MAX(CASE WHEN month_from_Date = 5 THEN Count_Of_OId END) '5_COID',
      MAX(CASE WHEN month_from_Date = 6 THEN Count_Of_OId END) '6_COID',
      MAX(CASE WHEN month_from_Date = 4 THEN Avg_a END) '4_Avg_a',
      MAX(CASE WHEN month_from_Date = 5 THEN Avg_a END) '5_Avg_a',
      MAX(CASE WHEN month_from_Date = 6 THEN Avg_a END) '6_Avg_a',
      MAX(CASE WHEN month_from_Date = 4 THEN Count_of_users END) '4_Count_of_u',
      MAX(CASE WHEN month_from_Date = 5 THEN Count_of_users END) '5_Count_of_u',
      MAX(CASE WHEN month_from_Date = 6 THEN Count_of_users END) '6_Count_of_u'
FROM [MyTable]
GROUP BY [team]

Upvotes: 3

Related Questions