How can I Pivot a table in DB2? [duplicate]

Question

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 1

   select Id,                                              
      max(case when Code = '1' then Value end) as Code1_Val,  
      max(case when Code = '2' then Value end) as Code2_Val,  
      max(case when Code = '3' then Value end) as Code3_Val   
      from TABLEA                                     
      group by Id                                            

Answer 2

SELECT Id,
max(DECODE(Code, 1, Value)) AS Code1_Val,
max(DECODE(Code, 2, Value)) AS Code2_Val,
max(DECODE(Code, 3, Value)) AS Code3_Val
FROM A
group by Id