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?
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
The currently accepted answer by bhamby is certainly correct, but it's worth checking if using several correlated subqueries is much slower than a single group by (hint: it most likely is):
SELECT
A.ItemID,
MAX(CASE WHEN A.Item = 'Meeting' THEN Value END) AS Meeting,
MAX(CASE WHEN A.Item = 'Advise' THEN Value END) AS Advise,
MAX(CASE WHEN A.Item = 'NoAdvise' THEN Value END) AS NoAdvise
FROM A
GROUP BY A.ItemID
It's also a bit simpler in my opinion
SQLFiddle (in PostgreSQL, but works on DB2 LUW as well)
As @bhamby said, DB2 doesn't have a PIVOT function.
Mostly, my query just differs in how the results are retrieved - you'd need to run the profiler/optimizer over them to be sure, but I believe that the correlated sub-queries may be executed per-row (potentially less efficient), rather than as sets. This is unlikely to be an issue over small datasets.
WITH Item (id) as (SELECT DISTINCT itemId
FROM YourTable),
SELECT item.id, Meeting.meeting, Advise.advise, NoAdvise.noadvise
FROM Item
LEFT JOIN (SELECT itemId, value as meeting
FROM YourTable
WHERE item = 'Meeting') as Meeting
ON Meeting.itemId = Item.id
LEFT JOIN (SELECT itemId, value as advise
FROM YourTable
WHERE item = 'Advise') as Advise
ON Advise.itemId = Item.id
LEFT JOIN (SELECT itemId, value as noadvise
FROM YourTable
WHERE item = 'NoAdvise') as NoAdvise
ON NoAdvise.itemId = Item.id
(... Actually, I'm a little concerned that you have columns for both 'advise' and 'no advise', which would appear to be some sort of boolean condition - ie, you should have one, but not the other).
