将seq列升序列值中的col_1分组,以显示以下示例数据的多列注释列中的值:
col_1 | Seq | Comment |
--------------------------|
ABC | 30 | TestC |
ABC | 50 | TestE |
ABC | 80 | TestG |
ABC | 10 | TestA |
ABC | 60 | TestF |
ABC | 20 | TestB |
ABC | 70 | TestF |
ABC | 40 | TestD |
DEF | 20 | TestB |
DEF | 10 | TestA |
GHI | 10 | TestA |
--------------------------|
Expected output of sql should be:
Col_1 | Col_2 | Col_3 | Col_4 | Col_5 | Col_6 | Col_7 | Col_8 |
-------|-------|-------|-------|-------|-------|-------|-------|
ABC | TestA | TestB | TestC | TestD | TestE | TestF | TestG |
DEF | TestA | TestB | | | | | |
GHI | TestA | | | | | | |
-------|-------|-------|-------|-------|-------|-------|-------|
发布于 2018-07-23 05:18:29
您可以使用条件聚合和row_number()
select col_1,
max(case when seqnum = 1 then comment end) as col_2,
max(case when seqnum = 2 then comment end) as col_3,
max(case when seqnum = 3 then comment end) as col_4,
. . .
from (select t.*,
row_number() over (partition by col_1 order by seq) as seqnum
from t
) t
group by col_1;
https://stackoverflow.com/questions/51469292
复制相似问题