Welcome to The Community. The community is designed to share information on SAP HANA in an interactive way.

Visit SAPHANATutorial.Com
Converting values of a column to different columns (transposing rows to columns)
  • Hi 

    Lets say I have a dataset like this. 

    ID  |    Item

    1      A

    2      B

    3      A

    1      C

    2      B

    2      B

    1      A

    3      B

    The actual dataset has 50K rows and 8.5K distinct Item values. 

    Now I want to transpose the rows to columns for each Id such that we are counting the values for each item for each user. Like below

    ID |   A   |   B  |  C  

    1      2         0     1

    2      0        3      0

    3      1        1      0

    Now if I have like set no. of Item values then I can hardcode them in case aggregate statement. 

    Something like 

    select ID, ITEM, count(case when Item ='A' then 1 Else NULL END) A, case (when Item='B' then 1 ELSE NULL END) B... so on

    But in this case I have 8.5K unique item. This would be too much task to do as above in sql. 

    Please advise. 

Howdy, Stranger!

It looks like you're new here. Sign In using your Gmail ID, Click on 'Google' button to sign in

Free Online Training Programs under SAPHANATutorial.com:
Click here to start SAP HANA Basics online course.
© 2016 : saphanatutorial.com, All rights reserved.
Visit SAPHANATutorial.Com