sql - 将对角线样式行转换为单行

Question

我有一些如下所示的排名表，表格元素看起来有点像对角线。

A            B           C     
------------------------------
Icecream     NULL        NULL
Waffle       NULL        NULL
Banana       NULL        NULL
NULL         Cherry      NULL
NULL         Apple       NULL
NULL         Coke        NULL
NULL         NULL        Banana
NULL         NULL        Cake
NULL         NULL        Waffle

我想像下面这样转换上表

A            B           C     
------------------------------
Icecream     Cherry      Banana
Waffle       Apple       Cake
Banana       Coke        Waffle

当元素为数字类型时，有一些将此样式表转换为单行样式表的示例。但是很难找到像这样的varchar风格

标签： sqloracle

Answer 1

按列将表格分解为单独的视图，然后按 ROWNUM 将视图重新组合在一起：

WITH cteA AS (SELECT ROWNUM AS RN, A FROM DATA_TABLE WHERE A IS NOT NULL),
     cteB AS (SELECT ROWNUM AS RN, B FROM DATA_TABLE WHERE B IS NOT NULL),
     cteC AS (SELECT ROWNUM AS RN, C FROM DATA_TABLE WHERE C IS NOT NULL)
SELECT A, B, C
  FROM cteA
  INNER JOIN cteB USING (RN)
  INNER JOIN cteC USING (RN)

如果你碰巧有一个不支持的数据库，WITH你可以对子选择做同样的事情：

SELECT A, B, C
  FROM (SELECT ROWNUM AS RN, A FROM DATA_TABLE WHERE A IS NOT NULL)
  INNER JOIN (SELECT ROWNUM AS RN, B FROM DATA_TABLE WHERE B IS NOT NULL)
    USING (RN)
  INNER JOIN (SELECT ROWNUM AS RN, C FROM DATA_TABLE WHERE C IS NOT NULL)
    USING (RN)

dbfiddle在这里