如何在Teradata Studio中使用PIVOT拼接结果？

需求描述 #

我有一张表，ID列（示例中为person）的每个值对应一行或多行数据，需要在SQL中实现行转列（PIVOT），同时将值列（示例中为relationship_with）按类别（relationship_type）拼接聚合。

R语言tidyverse实现示例 #

library(tidyverse)
df <- data.frame(
  person = c("Tina", "Tina", "Tina", "Rachel", "Rachel", "Rachel"),
  relationship_with = c("George", "Jamal", "Thomas", "Joe", "Taylor", "Bruce"),
  relationship_type = c("friend", "friend", "coworker", "friend", "coworker","coworker")
)
df |> pivot_wider(names_from = relationship_type, 
                  values_from = relationship_with,
                  values_fn = ~paste(., collapse=","))

输出结果：

# A tibble: 2 × 3
  person friend       coworker    
  <chr>  <chr>        <chr>       
1 Tina   George,Jamal Thomas      
2 Rachel Joe          Taylor,Bruce

尝试的Teradata SQL（报错） #

WITH cte AS (
  SELECT person, relationship_with, relationship_type
  FROM df
)
SELECT * 
FROM cte
PIVOT (
  CONCAT(relationship_with) FOR relationship_type IN (
   'friend',
   'coworker'
)
) AS pivot;

报错原因：Teradata的PIVOT子句仅支持AVG、MAX这类内置聚合函数，CONCAT不是聚合函数，无法在PIVOT中使用。

解决方案 #

Teradata提供了**LISTAGG**聚合函数用于分组拼接字符串，结合以下两种方式可实现需求：

方法一：CASE WHEN + LISTAGG手动行转列 #

直接通过条件分支筛选类别，再用LISTAGG完成拼接：

SELECT
  person,
  LISTAGG(CASE WHEN relationship_type = 'friend' THEN relationship_with END, ',') 
    WITHIN GROUP (ORDER BY relationship_with) AS friend,
  LISTAGG(CASE WHEN relationship_type = 'coworker' THEN relationship_with END, ',') 
    WITHIN GROUP (ORDER BY relationship_with) AS coworker
FROM df
GROUP BY person;

方法二：先聚合再PIVOT转列 #

先对person和relationship_type分组拼接，再用PIVOT转列：

WITH aggregated AS (
  SELECT
    person,
    relationship_type,
    LISTAGG(relationship_with, ',') WITHIN GROUP (ORDER BY relationship_with) AS rel_list
  FROM df
  GROUP BY person, relationship_type
)
SELECT *
FROM aggregated
PIVOT (
  MAX(rel_list)  -- 预聚合后每组仅一行，MAX/MIN/AVG均可
  FOR relationship_type IN ('friend' AS friend, 'coworker' AS coworker)
) AS pivot_result;

说明 #

• LISTAGG是Teradata专用于字符串聚合的函数，WITHIN GROUP (ORDER BY ...)可指定拼接顺序；
• 方法二中使用MAX是因为预聚合后每个person+relationship_type组合仅存在一行数据，MAX仅用于提取对应值，替换为MIN或AVG不会影响结果。

内容的提问来源于stack exchange，提问作者degeso