SQL 如何在 DB2 中透视表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13579143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I Pivot a table in DB2?
提问by Vicky
I have table A, below, where for each unique id, there are three codes with some value.
我有下面的表 A，其中对于每个唯一的 id，有三个具有某些值的代码。
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:
我有第二个表 B，格式如下：
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.
这里每个唯一 id 只有一行。我想从第一个表 A 中为第一个表中的每个 id 填充第二个表 B。
For the first table A above, the second table B should come out as:
对于上面的第一个表 A，第二个表 B 应为：
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?
如何在单个 SQL 查询中实现此目的？
采纳答案by Clockwork-Muse
If your version doesn't have DECODE(), you can also use this:
如果你的版本没有DECODE()，你也可以使用这个：
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).
（适用于我的 V6R1 DB2 实例，并有一个SQL Fiddle Example）。
回答by Najeev
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
回答by Esperento57
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
回答by Esperento57
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
回答by Shobu Oracle
Here is the SQL Query:
这是 SQL 查询：
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 ;
回答by valex
Here is a SQLFiddle example
这是一个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
回答by David Manheim
You want to pivot your data. Since DB2 has no pivot function, yo can use Decode (basically a case statement.)
您想对数据进行透视。由于 DB2 没有枢轴函数，所以您可以使用 Decode（基本上是一个 case 语句。）
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
