我有一个表,具有相同Name
的行最多可以出现5次。示例:
| Name | Value |
|--------|---------|
| Test | Value1 |
| Test | Value2 |
| Test | Value3 |
| FooBar | Value11 |
| FooBar | Value12 |
我正在尝试创建一个查询,以压缩行,使其具有唯一的Name
,并将值传输到列。如果每个名称的值少于5个,则其余列应为NULL。
| Name | Col1 | Col2 | Col3 | Col4 | Col5 |
|--------|---------|---------|--------|------|------|
| Test | Value1 | Value2 | Value3 | NULL | NULL |
| FooBar | Value11 | Value12 | NULL | NULL | NULL |
我查看了Pivot
,但没有要聚合的列
csv文件需要此格式
使用SQL Server 2016。
您可以使用row_number()
:构造列
select name,
max(case when seqnum = 1 then value end) as value_1,
max(case when seqnum = 2 then value end) as value_2,
max(case when seqnum = 3 then value end) as value_3,
max(case when seqnum = 4 then value end) as value_4,
max(case when seqnum = 5 then value end) as value_5
from (select t.*,
row_number() over (partition by name order by value) as seqnum
from t
) t
group by name;