teradata sql 将多个匹配项转置为其他列 [英] teradata sql pivot multiple occurrences into additional columns
本文介绍了teradata sql 将多个匹配项转置为其他列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的事情:
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屋!
查看全文