时间戳

首页 > 解决方案 > 使用 case 语句将多行连接成一个单列

sql - 使用 case 语句将多行连接成一个单列

问题描述

我必须用分布在多行中的逗号分隔值连接一列。根据行号，我必须连接文本列。例如对于文本列中的 row_num 2 值应该是 AB,BC

输入

Name.   Date      Identifier     Text Row_num   Val
Charles 08/07/18 30565   AB    1       2
Charles 08/07/18     30565   BC    2       4
Charles 08/07/18     30565   CD    3       6
Charles 08/07/18     30565   EF    4   3
Charles 08/07/18     30565   FG    5.  4
Charles 08/07/18     30565   GH    6       5

所需输出： 

Name    Date    Identifier  Text              Row_num        Val
Charles 08/07/18      30565     AB                  1         2
Charles 08/07/18      30565     AB,BC               2         4
Charles 08/07/18      30565     AB,BC,CD                3         6
Charles 08/07/18      30565     AB,BC,CD,EF         4         3
Charles 08/07/18      30565     AB,BC,CD,EF,FG      5         4
Charles 08/07/18      30565     AB,BC,CD,EF,FG,GF   7         5

使用的查询：

SELECT 
    Name,
    Date,
    ,Identifier
    MAX(CASE WHEN Row_nnum = 1 THEN Text END)  ||
    MAX(CASE WHEN Row_nnum = 2  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 3  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 4  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 5  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 6  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 7  THEN ', ' || Text ELSE '' END)
    SUM(val)
FROM TABLE 
GROUP BY 1,2,3;

此查询的问题是它在文本列中为 row_num 2、3、4、5、7 提供 Null 值。因此无法获得所需的输出

SELECT 
    Name,
    Date,
    ,Identifier
    MAX(CASE WHEN Row_nnum = 1 THEN Text END)  ||
    MAX(CASE WHEN Row_nnum = 2  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 3  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 4  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 5  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 6  THEN ', ' || Text ELSE '' END) ||
    MAX(CASE WHEN Row_nnum = 7  THEN ', ' || Text ELSE '' END),
    SUM(val)
FROM TABLE 
GROUP BY 1,2,3;

标签： sqlconcatenationcasegroup-concatvertica

解决方案

只需将该条件添加到CASE：

SELECT Name, Date, Identifier,
       (MAX(CASE WHEN Row_nnum = 1 THEN Text END)  ||
        MAX(CASE WHEN Row_nnum = 2 AND Text IS NOT NULL THEN ', ' || Text ELSE '' END) ||
        MAX(CASE WHEN Row_nnum = 3 AND Text IS NOT NULL THEN ', ' || Text ELSE '' END) ||
        MAX(CASE WHEN Row_nnum = 4 AND Text IS NOT NULL THEN ', ' || Text ELSE '' END) ||
        MAX(CASE WHEN Row_nnum = 5 AND Text IS NOT NULL THEN ', ' || Text ELSE '' END) ||
        MAX(CASE WHEN Row_nnum = 6 AND Text IS NOT NULL THEN ', ' || Text ELSE '' END) ||
        MAX(CASE WHEN Row_nnum = 7 AND Text IS NOT NULL THEN ', ' || Text ELSE '' END)
       ),
      SUM(val)
FROM TABLE 
GROUP BY 1, 2, 3;

推荐阅读