Make column values into headers

Question

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!

Here is the link for using pivot with SQL Server: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — mungea05, Aug 13 '14 at 18:36
I'm still confused on how to implement pivot in this example. — user3703758, Aug 13 '14 at 18:55

SQLChao · Accepted Answer · 2014-08-13T20:20:39.537

1

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

edited Aug 13 '14 at 20:20

answered Aug 13 '14 at 19:27

SQLChao

7,709
1
17
32

Thanks for the help JChao! How could I display both records on the same column or create two records with the same information. Assume there are two items that fall under the same class for example Most ideal: Order 220132 SCHGP: Class 2, Item: ABC ABD. Acceptable: Order 220132 SCHGP: Class 2, Item: ABC Order 220132 SCHGP: Class 2, Item: ABD – user3703758 Aug 13 '14 at 19:34
you have to use a subselect using xml – John Ruddell Aug 13 '14 at 20:03
Can you provide an example John? – user3703758 Aug 13 '14 at 20:10
@user3703758 i've updated the answer. What John was suggesting is simulating group concat. So I did this in the base dataset and then pivoted the data. I've updated the SQL fiddle example with sample data where a order has more than one class 1. – SQLChao Aug 13 '14 at 20:21
Thank you so much JChao, Can you explain what xml path does? – user3703758 Aug 13 '14 at 20:36
I'm no good a explaining it lol. Basically building XML from the data in the column. I think it's easier to see an example. `select cast(' ' as varchar(max)) + item from testpivot for xml path ('test')` – SQLChao Aug 13 '14 at 20:44

Make column values into headers

1 Answers1