Pivoting in DB2

Question

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 1

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

Answer 2

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)

Answer 3

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).