Mike Mike - 1 year ago 48
SQL Question

Pivoting in DB2

I have to transpose my rows into columns from a DB2 table.This is how my table is structured..

ItemID Item Value
---------------------
1 Meeting Now
1 Advise Yes
1 NoAdvise No
2 Meeting Never
2 Advise No
2 NoAdvise Null
2 Combine Yes


I want this to be transposed into(note that I do not want to transpose Combine)

ItemID Meeting Advise NoAdvise
---------------------------------------
1 Now Yes No
2 Never No Null


Bit struggling with the query, can you please help?

Answer Source

It's not very pretty, but it should work. DB2 doesn't have a built-in PIVOT function, like SQL Server.

SELECT DISTINCT
     A.ItemID
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'Meeting'
    ) AS Meeting
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'Advise'
    ) AS Advise
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'NoAdvise'
    ) AS NoAdvise
FROM table A
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download