Vicky Vicky - 1 month ago 24
SQL Question

How can I Pivot a table in DB2?

I have table A, below, where for each unique id, there are three codes with some value.

ID Code Value
---------------------
11 1 x
11 2 y
11 3 z
12 1 p
12 2 q
12 3 r
13 1 l
13 2 m
13 3 n


I have a second table B with format as below:

Id Code1_Val Code2_Val Code3_Val


Here there is just one row for each unique id. I want to populate this second table B from first table A for each id from the first table.

For the first table A above, the second table B should come out as:

Id Code1_Val Code2_Val Code3_Val
---------------------------------------------
11 x y z
12 p q r
13 l m n


How can I achieve this in a single SQL query?

Answer

If your version doesn't have DECODE(), you can also use this:

INSERT INTO B (id, code1_val, code2_val, code3_val)  
WITH Ids (id) as (SELECT DISTINCT id
                  FROM A) -- Only to construct list of ids

SELECT Ids.id, a1.value, a2.value, a3.value
FROM Ids -- or substitute the actual id table
JOIN A a1
     ON a1.id = ids.id
        AND a1.code = 1
JOIN A a2
     ON a2.id = ids.id
        AND a2.code = 2
JOIN A a3
     ON a3.id = ids.id
        AND a3.code = 3

(Works on my V6R1 DB2 instance, and have an SQL Fiddle Example).