SQL statement to transform a column to table header

Question

Here is my table

 CustomerName|Items 
 customer 1  |Item 1
 customer 1  |Item 2
 customer 1  |Item 3
 customer 2  |Item 1
 customer 2  |Item 4
 customer 3  |Item 2
 customer 4  |Item 5
 customer 5  |Item 6

I need to transform the table to

 CustomerName|Item 1|Item 2|Item 3|Item 4|Item 5|Item 6|
 customer 1  |  X   |  X   |  X   |      |      |      |
 customer 2  |  X   |      |      |  X   |      |      |
 customer 3  |      |  X   |      |  X   |      |      |
 customer 4  |      |      |      |      |  X   |      |
 customer 5  |      |      |      |      |      |  X   |

I tried PIVOT, in SAP HANA it's not working.

Any suggestions would be much appreciated.

are there `6` items only or there could be more. In that case do you want to add that many columns in the table? — Utsav, Mar 20 '17 at 14:50
There could be hundreds of thousands of Items :) its just an example. — sveer, Mar 20 '17 at 14:54
See http://stackoverflow.com/questions/7674786/mysql-pivot-table. — Tom, Mar 20 '17 at 14:55
If you could have hundreds of thousands of items, you wouldn't want to do it in MySQL. Do you really want a pivot table with that many? — Tom, Mar 20 '17 at 15:01
@Tom Not really! I have no clue which method i shall follow to transform. I checked the page you shared already. — sveer, Mar 20 '17 at 15:03
@Knut SAP Hana and I use sql queries for data retrieval according to requirements. — sveer, Mar 20 '17 at 23:10
@Utsav and Tom i may little exaggerate, the items available are finite. The number of items changes time to time. so the query should be dynamic. currently there are around 2000 items. — sveer, Mar 20 '17 at 23:16
I'm not sure what you are trying to accomplish. Do you want to look at a pivot table that is 2000 columns wide? How would you use that? It doesn't seem like something anyone would want to look at. — Tom, Mar 22 '17 at 14:53
This would be pretty horrible to create in SQL. I would recommend using a reporting or BI tool to accomplish this. — Tom, Mar 22 '17 at 19:55

score 2 · Accepted Answer · answered Mar 20 '17 at 23:52

SAP HANA doesn't provide the non-standard SQL operator "PIVOT" that can be found in other DBMS. For a permanent transformation, e.g. during ETL you can use the flowgraph function PIVOT/UNPIVOT to automatically perform the transformation.

For on-the-fly transformation, there is no built-in feature and you'll have to code it yourself.

score 1 · Answer 2 · answered Mar 20 '17 at 20:22

Ok, try this:

SELECT CustomerName,
   MAX(CASE WHEN ROWN=1 THEN "Items" ELSE NULL END) AS Item1,
   MAX(CASE WHEN ROWN=2 THEN "Items" ELSE NULL END) AS Item2,
   MAX(CASE WHEN ROWN=3 THEN "Items" ELSE NULL END) AS Item3,
   MAX(CASE WHEN ROWN=4 THEN "Items" ELSE NULL END) AS Item4,
   MAX(CASE WHEN ROWN=5 THEN "Items" ELSE NULL END) AS Item5,
   MAX(CASE WHEN ROWN=6 THEN "Items" ELSE NULL END) AS Item6,
FROM 
  (SELECT CustomerName, Items, 
          row_number() over (PARTITION BY CustomerName) AS ROWN
FROM TABLE_NAME ORDER BY CustomerName)
                GROUP BY CustomerName;

Where TABLE_NAME is the name of your source table.

SQL statement to transform a column to table header

2 Answers2