在redshift SQL中，如何为一列中的每个唯一值创建一个新列

Question

我有一个表，看起来像：

值

键
1	A
1	B
2	A
2	B
2	C
2	D

如果你知道你有多少个值，你可以在一个组中完成：

SELECT Key,
MAX(CASE WHEN Value = 'A' THEN Value ELSE '-' END) AS Value1,
MAX(CASE WHEN Value = 'B' THEN Value ELSE '-' END) AS Value2,
MAX(CASE WHEN Value = 'C' THEN Value ELSE '-' END) AS Value3,
MAX(CASE WHEN Value = 'D' THEN Value ELSE '-' END) AS Value4
FROM
TABLE_NAME
GROUP BY Key

如果值的数目未知，则不能使用泛型代码。这是由于列不是无限制的。https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_usage.html

另一种方法是按一定顺序(按字母顺序(分配列。以下是展开数据时的情况(键=3已添加并扩展到20个可能的列(。

CREATE TABLE t1 
AS
SELECT 1 AS KEY,'A' AS Value UNION SELECT 1, 'B' 
UNION SELECT 2, 'A' UNION SELECT 2, 'B' UNION SELECT 2, 'C' UNION SELECT 2, 'D' 
UNION SELECT 3, 'X' UNION SELECT 3, 'Y' UNION SELECT 3, 'Z' 
;
select key,
max(decode(rn, 1, value, '-')) as v1, 
max(decode(rn, 2, value, '-')) as v2,
max(decode(rn, 3, value, '-')) as v3,
max(decode(rn, 4, value, '-')) as v4,
max(decode(rn, 5, value, '-')) as v5,
max(decode(rn, 6, value, '-')) as v6,
max(decode(rn, 7, value, '-')) as v7,
max(decode(rn, 8, value, '-')) as v8, 
max(decode(rn, 9, value, '-')) as v9,
max(decode(rn, 10, value, '-')) as v10,
max(decode(rn, 11, value, '-')) as v11,
max(decode(rn, 12, value, '-')) as v12,
max(decode(rn, 13, value, '-')) as v13,
max(decode(rn, 14, value, '-')) as v14,
max(decode(rn, 15, value, '-')) as v15, 
max(decode(rn, 16, value, '-')) as v16,
max(decode(rn, 17, value, '-')) as v17,
max(decode(rn, 18, value, '-')) as v18,
max(decode(rn, 19, value, '-')) as v19,
max(decode(rn, 20, value, '-')) as v20
from (
select key, value, row_number() over (partition by key order by value) as rn
from t1 )
group by key
order by key;

注意，由于可读性更好，我使用了DECODE((语句而不是CASE。您提到了Redshift，所以这将在那里工作，但对于其他数据库，您可能需要恢复到CASE。

这种方法的结果看起来像：

key v1  v2  v3  v4  v5  v6  v7  v8  v9  v10 v11 v12 v13 v14 v15 v16 v17 v18 v19 v20
1   A   B   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
2   A   B   C   D   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
3   X   Y   Z   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -