title: Converting rows to columns using multiple pivot in sql
body: <p>I have written a query to convert rows into columns using multiple pivot functions with respect to months 4, 5 &amp; 6. I did succeed in converting the rows into columns. Below is the query:</p>
  
  <pre><code>(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
  </code></pre>
  
  <p>So the output was: </p>
  
  <pre><code>+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
  |  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         |
  +--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
  |  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 |
  +--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+--------------+--------------+
  </code></pre>
  
  <p>I am not sure, whats the mistake in my code. </p>