3

Here is an example table that i have

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 its not working.

Any suggestions would be much appreciated.

vwegert
  • 18,303
  • 3
  • 35
  • 56
sveer
  • 420
  • 3
  • 15
  • 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
  • 1
    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
  • Do you use MySQL or SAP Hana? – knut Mar 20 '17 at 19:30
  • @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
  • @Tom, Its a requirement :) – sveer Mar 22 '17 at 19:43
  • 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

2 Answers2

2

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.

Lars Br.
  • 9,181
  • 2
  • 14
  • 28
1

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.

OctoCode
  • 362
  • 4
  • 13