teradata sql将多次出现的数据透视到其他列中 [英] teradata sql pivot multiple occurrences into additional columns

问题描述

我有这样的东西:

ID      Result
1       value1
2       value1
2       value2
3       value1
4       value1
4       value2
4       value3

我想返回这样的内容:

ID      Result1      Result2      Result3
1       value1
2       value1       value2
3       value1
4       value1       value2       value3

我已经搜索过关键点，连贯点和中断点，但我找不到简单，明智的解决方案.

I've searched on pivots and concats and breaks and I just can't find a simple, sensible solution.

TIA

推荐答案

不幸的是，Teradata没有PIVOT函数，但是您可以将聚合函数与CASE表达式一起使用来获取结果.

Unfortunately Teradata doesn't have a PIVOT function but you can use an aggregate function with a CASE expression to get the result.

select id,
    max(case when seq =1 then result end) result1,
    max(case when seq =2 then result end) result2,
    max(case when seq =3 then result end) result3
from
(
    select id, res, row_number() over(partition by id order by result) seq
    from yourtable
) d
group by id
order by id;

如果每个ID都有更多值，则可以添加更多CASE表达式.

If you have more values for each ID, then you can add more CASE expressions.

这篇关于teradata sql将多次出现的数据透视到其他列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！