更新时间:2023-02-03 11:01:19
使用PIVOT函数时,IN子句中的值必须与您选择的值匹配.您当前的数据不包括1、2或3.您可以使用row_number()
为每个x
分配一个值:
When you are using the PIVOT function the values inside the IN clause need to match a value that you are selecting. Your current data does not include 1, 2, or 3. You can use row_number()
to assign a value for each x
:
select x, [1], [2], [3]
from
(
select x, value,
row_number() over(partition by x order by y) rn
from test
) d
pivot
(
max(value)
for rn in ([1], [2], [3])
) piv;
请参见带演示的SQL提琴.如果每个x
的值数量未知,那么您将要使用动态SQL:
See SQL Fiddle with Demo. If you then have a unknown number of values for each x
, then you will want to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(row_number() over(partition by x order by y))
from test
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT x,' + @cols + '
from
(
select x, value,
row_number() over(partition by x order by y) rn
from test
) x
pivot
(
max(value)
for rn in (' + @cols + ')
) p '
execute(@query);
请参见带有演示的SQL提琴