sql - How to transpose multiple rows into multiple columns?
问题描述
Grouping col_1 in ascending values of seq column to display values from comment columns in multiple columns for below sample data:
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 | | | | | | |
-------|-------|-------|-------|-------|-------|-------|-------|
解决方案
您可以使用条件聚合和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;
推荐阅读
- javascript - 您如何在页面上水平居中引导模式?
- error-handling - 如何完全卸载 RUNDECK
- r - 如何将 ggplot 导出为可调整大小的图像?
- c# - WPF 获取选中的 ComboBoxItem
- amazon-web-services - 标记资源的 AWS CDK 策略条件
- angular-formly - 自定义模型值/输出 ngx-formly
- rpmbuild - ACE/TAO 构建成功后的 rpm
- laravel - 导入外部种子数据
- gremlin - 如何使用 GremlinAPI 在 CosmosDB 中进行简单计算
- java - 属性为null时未调用Spring Boot父(2.3.0.RELEASE)异常处理方法