sql-server - 按第一列分组，然后拆分(透视？)剩余两列

Question

这里有 TSQL 问题。请参阅下图中的源和所需的输出。还提供了构建源表的代码。

<小时/>

DECLARE @tablevar TABLE(
record nvarchar(10),
category nvarchar(50),
value float)

INSERT INTO @tablevar
VALUES
('110-AL','credits_cle',1),
('110-AL','credits_ethics',2),
('110-AR','credits_ethics',2.5),
('110-AZ','credits_prof_resp',1.5),
('110-AZ', 'credits_ethics',5),
('110-AZ', 'credits_cle',4)

Answer 1

由于您想要对两列数据进行透视，一种方法是同时应用 UNPIVOT 和 PIVOT 函数。 UNPIVOT 会将多列 category 和 value 转换为多行，然后您可以应用 PIVOT 来获取最终结果:

select record, 
  category1, value1, 
  category2, value2, 
  category3, value3
from
(
  select record, col+cast(seq as varchar(10)) col, val
  from
  (
    select record, category, 
      cast(value as nvarchar(50)) value,
      row_number() over(partition by record order by category) seq
    from tablevar
  ) d
  unpivot
  (
    val
    for col in (category, value)
  ) unpiv
) src
pivot
(
  max(val)
  for col in (category1, value1, category2, value2, category3, value3)
) piv;

参见SQL Fiddle with Demo .

如果您有未知数量的值，那么您将必须使用类似于以下的动态 SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by record order by category) seq
                      from tablevar
                    ) d
                    cross apply
                    (
                      select 'category', 1 union all
                      select 'value', 2
                    ) c (col, so)
                    group by seq, so, col
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT record,' + @cols + ' 
             from 
             (
               select record, col+cast(seq as varchar(10)) col, val
                from
                (
                  select record, category, 
                    cast(value as nvarchar(50)) value,
                    row_number() over(partition by record order by category) seq
                  from tablevar
                ) d
                unpivot
                (
                  val
                  for col in (category, value)
                ) unpiv
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + ')
            ) p '

execute(@query);

参见SQL Fiddle with Demo