Geek Answers Handbook

How can I rotate a table in DB2?

Below is Table A, where for each unique identifier 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 the format as shown below:

 Id Code1_Val Code2_Val Code3_Val

There is only one line for each unique identifier. I want to populate this second table B from the first table A for each identifier from the first table.

For the first table A above, the second table B should look like:

 Id Code1_Val Code2_Val Code3_Val --------------------------------------------- 11 xyz 12 pqr 13 lmn

How can I achieve this in a single SQL query?

+6
source share
7 answers

If your version does not 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 DB2 V6R1 instance and has an example SQL script ).

+2
source 
  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
+6
source 
 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
+1
source

Below is an example of SQLFiddle

 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
0
source

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 ;
0
source 
  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
0
source

You want to change your data. Since DB2 does not have a rotation function, you 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
-1
source

More articles:


All Articles