SQL Server - Pivot 将行转换为列(带有附加行数据) [英] SQL Server - Pivot Convert rows to columns (with additional row data)

问题描述

有人可以帮我处理这个 SQL 吗?

Can someone help me with this SQL?

我希望通过以下方式将多行转为列:id、类型、颜色、日期

I am looking to pivot multiple rows into columns by: id, type, color, date

挑战:我想在透视表上保留/显示多列.

Challenge: I have multiple columns I want to preserve/be shown on the pivoted table.

id | type   | color  | date      | country_code | cost
---+--------+--------+-----------+--------------+-------
1  | report | red    | 2020-09-01| US           | 1
1  | report | red    | 2020-09-01| EU           | 2
1  | report | red    | 2020-09-01| RU           | 3
1  | report | red    | 2020-09-01| AP           | 4
1  | report | blue   | 2020-09-02| US           | 5   
1  | report | blue   | 2020-09-02| EU           | 6
1  | report | blue   | 2020-09-02| RU           | 7
1  | report | blue   | 2020-09-02| AP           | 8
2  | report | green  | 2020-09-02| US           | 9
2  | report | green  | 2020-09-02| EU           | 10
2  | report | green  | 2020-09-02| RU           | 11
2  | report | green  | 2020-09-02| AP           | 12
2  | report | blue   | 2020-09-03| US           | 13
2  | report | blue   | 2020-09-03| EU           | 14
2  | report | blue   | 2020-09-03| RU           | 15
2  | report | blue   | 2020-09-03| AP           | 16

所需的输出:

id | type   | color | date       | US | EU | RU | AP
---+--------+-------+------------+----+----+----+----
1  | report | red   | 2020-09-01 | 1  | 2  | 3  | 4
1  | report | blue  | 2020-09-02 | 5  | 6  | 7  | 8
2  | report | green | 2020-09-02 | 9  | 10 | 11 | 12
2  | report | blue  | 2020-09-03 | 13 | 14 | 15 | 16

已知信息:

• 只有 4 个国家/地区代码.
• color 值将相同:id、type、date.

• there are only 4 country codes.
• the color value will be same per: id, type, date.

不确定编写此 SQL 的最干净/最好的方法是什么.

Not sure what is the most cleanest/best way to write this SQL.

我尝试使用

ROW_NUMBER() OVER(PARTITION BY xxx ORDER BY yyy)

和 PIVOT 但无法得到我想要的结果

and PIVOT but couldn't get the results I was looking for

推荐答案

我认为条件聚合可以满足您的需求:

I think conditional aggregation does what you want:

select id, type, color, date,
       max(case when country_code = 'US' then cost end) as us,
       max(case when country_code = 'EU' then cost end) as eu,
       max(case when country_code = 'RU' then cost end) as ru,
       max(case when country_code = 'AP' then cost end) as AP
from t
group by id, type, color, date;

这篇关于SQL Server - Pivot 将行转换为列(带有附加行数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！