动态数据透视(行至列) [英] Dynamic Pivot (row to columns)

问题描述

我有一个Table1:

ID Instance Name Size Tech
 1   0       D1  123   ABC
 1   1       D2  234   CDV
 2   2       D3  234   CDV
 2   3       D4  345   SDF

我需要使用动态PIVOT的结果集与标题一起显示:

I need the resultset using Dynamic PIVOT to look like along with the headers:

ID | Instance0_Name | Instance0_Size | Instance0_Tech | Instance1_Name | Instance1_Size | Instance1_tech
1  | D1             | 123            | ABC            | D2             | 234            | CDV

任何帮助将不胜感激.使用Sql Server 2008.

Any help would be appreciated. using Sql Server 2008.

很抱歉，以前的帖子.

推荐答案

您所需的输出并不十分清楚，但是您可以同时使用UNPIVOT和PIVOT函数来获取结果

Your desired output is not exactly clear, but you can use the both the UNPIVOT and PIVOT function to get the result

如果您知道列数，则可以对值进行硬编码:

If you know the number of columns, then you can hard code the values:

select *
from
(
  select id, 
    'Instance'+cast(instance as varchar(10))+'_'+col col, 
    value
  from 
  (
    select id, 
      Instance, 
      Name, 
      cast(Size as varchar(50)) Size,
      Tech
    from yourtable
  ) x
  unpivot
  (
    value
    for col in (Name, Size, Tech)
  ) u
) x1
pivot
(
  max(value) 
  for col in
    ([Instance0_Name], [Instance0_Size], [Instance0_Tech], 
     [Instance1_Name], [Instance1_Size], [Instance1_Tech], 
     [Instance2_Name], [Instance2_Size], [Instance2_Tech], 
     [Instance3_Name], [Instance3_Size], [Instance3_Tech])
) p

请参见带有演示的SQL小提琴

然后，如果您有未知数量的值，则可以使用动态sql:

Then if you have an unknown number of values, you can use dynamic sql:

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT ',' 
                      + quotename('Instance'+ cast(instance as varchar(10))+'_'+c.name)
                    from yourtable t
                    cross apply sys.columns as C
                    where C.object_id = object_id('yourtable') and
                         C.name not in ('id', 'instance')
                    group by t.instance, c.name
                    order by t.instance
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select *
      from
      (
        select id, 
          ''Instance''+cast(instance as varchar(10))+''_''+col col, 
          value
        from 
        (
          select id, 
            Instance, 
            Name, 
            cast(Size as varchar(50)) Size,
            Tech
          from yourtable
        ) x
        unpivot
        (
          value
          for col in (Name, Size, Tech)
        ) u 
      ) x1
      pivot
      (
        max(value)
        for col in ('+ @colspivot +')
      ) p'

exec(@query)

请参见带有演示的SQL小提琴

如果结果不正确，则请编辑您的OP并从您提供的两个ID中发布期望的结果.

If the result is not correct, then please edit your OP and post the result that you expect from both of the Ids you provided.

这篇关于动态数据透视(行至列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！