且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

SQL Server 2008 R2 使用 PIVOT 和 varchar 列不起作用

更新时间:2023-01-30 11:12:40

当您使用 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 Fiddle with Demo.如果您对每个 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 Fiddle with Demo