How can I Pivot a table in DB2?

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?

score 12 · Answer 1 · edited Apr 21 '14 at 23:29

12

   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

edited Apr 21 '14 at 23:29

Lucas Zamboulis

2,494
5
24
27

answered Apr 21 '14 at 23:03

Najeev

121
1
2

score 4 · Answer 2 · answered Nov 06 '16 at 12:22

4

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

answered Nov 06 '16 at 12:22

Esperento57

16,521
3
39
45

score 3 · Accepted Answer · answered Nov 27 '12 at 19:37

3

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

answered Nov 27 '12 at 19:37

Clockwork-Muse

12,806
6
31
45

is there a way to do this dynamically if table A gets a new code or two? – John Oct 18 '13 at 14:19
Not easily, and you'd need dynamic SQL too. I'm pretty sure there are examples elsewhere on here - essentially, you have to read the 'system information' schema tables, assemble the query that way, then execute it. Given that overall insertions to `A` should be pretty rare, it'd probably be easier to just update the statements and client code... Otherwise, you're verging into EAV territory, which is (somewhat) related but a bit different. – Clockwork-Muse Oct 19 '13 at 10:04
[There's a better answer that accesses the table only once](https://stackoverflow.com/a/23207503/521799) – Lukas Eder Jul 27 '17 at 13:14

score 0 · Answer 4 · answered Nov 27 '12 at 07:23

0

Here is a SQLFiddle example

insert into B (ID,Code1_Val,Code2_Val,Code3_Val)
select Id, max(V1),max(V2),max(V3) from
(
select ID,Value V1,'' V2,'' V3 from A where Code=1
union all
select ID,'' V1, Value V2,'' V3 from A where Code=2
union all
select ID,'' V1, '' V2,Value V3 from A where Code=3
) AG
group by ID

answered Nov 27 '12 at 07:23

valex

23,966
7
43
60

This is supposed to be for DB2 Specifically. SQLFiddle doesn't support DB2... – David Manheim Nov 27 '12 at 07:29
Yes sure. This SQL command is SQL-92 compatible so all DBMS's have to support this. – valex Nov 27 '12 at 07:45
But it's not a good SQL query for any DBMS that supports even a case statement, because it needs to do way more work than is warranted. (Group by, after doing 3 selects and a maximum call on 3 different columns...) – David Manheim Nov 27 '12 at 19:58

David Manheim · Answer 5 · 2019-04-29T07:53:30.433

0

You want to pivot your data. Since DB2 has no pivot function, yo can use Decode (basically a case statement.)

The syntax should be:

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

edited Apr 29 '19 at 07:53

answered Nov 27 '12 at 07:27

David Manheim

2,553
2
27
42

2

Dependent on version of DB2; my V6R1 (iSeries) version does not. – Clockwork-Muse Nov 27 '12 at 18:18
Thanks. You could update this statement to use a case statement, and avoid multiple joins. – David Manheim Nov 27 '12 at 20:00
2

you have a "," after Code3_Val – Esperento57 Nov 06 '16 at 12:26

score 0 · Answer 6 · edited Mar 20 '15 at 07:20

0

Here is the SQL Query:

insert into pivot_insert_table(id,code1_val,code2_val, code3_val) 
select * from (select id,code,value from pivot_table)
pivot(max(value) for code in (1,2,3)) order by id ;

edited Mar 20 '15 at 07:20

James Wong

4,529
4
48
65

answered Mar 20 '15 at 06:55

Shobu Oracle

1

Esperento57 · Answer 7 · 2017-05-30T17:42:07.923

0

 WITH Ids (id) as 
 (
  SELECT DISTINCT id FROM A
  )
 SELECT Ids.id, 
 (select sub.value from A sub where Ids.id=sub.id and sub.code=1 fetch first rows only) Code1_Val,

 (select sub.value from A sub where Ids.id=sub.id and sub.code=2 fetch first rows only) Code2_Val,

 (select sub.value from A sub where Ids.id=sub.id and sub.code=3 fetch first rows only) Code3_Val
 FROM Ids

edited May 30 '17 at 17:42

answered Nov 06 '16 at 12:31

Esperento57

16,521
3
39
45

How can I Pivot a table in DB2?

7 Answers7

Linked