如何在 DB2 中透视表? [英] How can I Pivot a table in DB2?

问题描述

我有下面的表 A，其中对于每个唯一 id，有三个具有某些值的代码.

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

我有第二张表 B，格式如下:

I have a second table B with format as below:

Id   Code1_Val   Code2_Val    Code3_Val

这里每个唯一 ID 只有一行.我想为第一个表中的每个 id 从第一个表 A 填充第二个表 B.

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.

对于上面的第一个表 A，第二个表 B 应该是:

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

如何在单个 SQL 查询中实现这一点?

How can I achieve this in a single SQL query?

推荐答案

如果你的版本没有DECODE()，你也可以用这个:

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

(适用于我的 V6R1 DB2 实例，并且有一个 SQL Fiddle 示例).

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

这篇关于如何在 DB2 中透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！