移动sql中相同行但不同列中具有相同ID的不同行的数据

Question

我有一个类似

的数据

  ID   ded1   ded2   ded3   ded4 
  ------------------------------
  1    2
  1    3
  1    4 

我想:

ID   ded1   ded2   ded3   ded4
------------------------------
1    2      3      4

ded4为空白，因为有3个值，如果有第4个值，则ded4填充

Answer 1

当且仅当您的列数量有限，并且您觉得不需要一个动态地将值分配给列的函数时，您可以这样做:

使用像row_number()这样的分析函数来确定顺序-哪个值应该放在哪个列下，然后将结果按id分组，并取max()的值从CASE语句中取出一行。

SELECT
  id,
  MAX(CASE WHEN ded_rn = 1 THEN ded1 END) AS ded1,
  MAX(CASE WHEN ded_rn = 2 THEN ded1 END) AS ded2,
  MAX(CASE WHEN ded_rn = 3 THEN ded1 END) AS ded3,
  MAX(CASE WHEN ded_rn = 4 THEN ded1 END) AS ded4
FROM(
  SELECT
    *,
    row_number() OVER (PARTITION BY id ORDER BY ded1) AS ded_rn
  FROM 
    tbl
   ) foo
GROUP BY id

Answer 2

虽然@考虑的答案是非常正确的，你也可以做4个连接，如果你有问题的组或有额外的列，你想从源表中包括。有时这是唯一可行的解决方案。(顺便说一句，pivot和这段代码基本相同)。

这个代码比@CondiderMe的答案稍微慢一点，因为它需要3次扫描/搜索，而他只有2次。所以只有在需要的时候才可以使用。

With addRN AS
(
  SELECT *,
        row_number() OVER (PARTITION BY id ORDER BY ded1) AS rn
  FROM tbl
)
SELECT ids.id, r1.ded1 as ded1, r2.ded1 as ded2, r3.ded1 ad ded3, r4.ded1 as ded4
FROM (SELECT DISTINCT id FROM tbl) ids
LEFT JOIN addRN r1 ON ids.id = r1.id AND rn = 1
LEFT JOIN addRN r2 ON ids.id = r2.id AND rn = 2
LEFT JOIN addRN r3 ON ids.id = r3.id AND rn = 3
LEFT JOIN addRN r3 ON ids.id = r3.id AND rn = 4