CODEWITHSUNDEEP
sqlsql-server-2008pivot
user3703758
user3703758

Reputation: 5

Make column values into headers

Please help me with this as I'm still new using SQL Server. I have the following table 

  Order  |  SCHGP  |      Item       |
+--------+---------+-----------------+
| 220132 | Class 1 | 0121R00007PDG   |
| 220132 | Class 2 | 0201R00046      |
| 220132 | Class 3 | 4400464PLGB     |
| 220132 | Class 4 | 4400466PLGB     |
|        | Class 5 | 4400468PLG      |
| 220132 | Class 6 | 0121R00013PDG   |
| 220167 | Class 1 | 0121R00006PNG   |
| 220167 | Class 2 | 0201R00338      |
| 220167 | Class 3 | 0121R00344PNG   |
| 220167 | Class 4 | 0121R00352PNG   |
| 220167 | Class 5 | 0121R00010PNG   |
+--------+---------+-----------------+

and desire to get the following(Basically group by orders and make the SCHGP column into headers):

║  Order ║    Class 1    ║  Class 2   ║    Class 3    ║    Class 4    ║    Class 5    ║    Class 6    ║
╠════════╬═══════════════╬════════════╬═══════════════╬═══════════════╬═══════════════╬═══════════════╣
║ 220132 ║ 0121R00007PDG ║ 0201R00046 ║ 4400464PLGB   ║ 4400466PLGB   ║ 4400468PLG    ║ 0121R00013PDG ║
║ 220167 ║ 0121R00006PNG ║ 0201R00338 ║ 0121R00344PNG ║ 0121R00352PNG ║ 0121R00010PNG ║               ║
╚════════╩═══════════════╩════════════╩═══════════════╩═══════════════╩═══════════════╩══════════════

Any tips would be appreciated!

Upvotes: 0

Views: 103

Answers (1)

SQLChao
SQLChao

Reputation: 7847

Based on your example data and result here's a PIVOT in SQL Server.

SQL Fiddle Demo

SELECT * 
FROM   
  (select [order], schgp, STUFF(
      (
        select cast(' ' as varchar(max)) + item
        from testpivot t1
        where t1.[order] = t2.[order] and t1.schgp = t2.schgp
        for xml path('')
      ), 1, 1, '') AS item
from testpivot t2
) a 
PIVOT 
  (Max(item) 
   FOR schgp IN ([Class 1], [Class 2], [Class 3], [Class 4], [Class 5], [Class 6])) AS pvt

Upvotes: 1

Related Questions