将多行转换为具有指定标题的列 [英] Converting multiple rows to columns with specified heading

问题描述

我有一个表格，其中包含个人进行的活动的详细信息-该表格的内容类似于以下内容:

I have a table that holds details of activities carried out by individuals - contents of this table is similar to the following:

| Person    | Category  | Activity   |
--------------------------------------
| Username1 | X         | X1         |
| Username1 | Y         | Y1         |
| Username1 | Z         | Z1         |

我需要一个SQL查询，该查询可以产生类似以下内容的内容，我们将不胜感激:

I need a SQL query that can produce something like the following and any help would be appreciated:

| Person    | Cat1 | Cat1_Act|Cat2 | Cat2_Act| Cat3  | Cat3_Act |
---------------------------------------------------------------
| Username1 | X    | X1      | Y   | Y1      | Z     |    Z1    |

我了解了许多文章，可以使用PIVOT来实现这一目标，但是我并没有找到接近我所需解决方案的解决方案，因为大多数解决方案通常都使用"X"，"Y"， "Z"(在我的示例表中)作为表头，但理想情况下，我希望能够为包含新列的表头指定名称(希望这很有意义，有人可以提供帮助:-))

I understand reading through a number of posts that PIVOT can be used to achieve this but I have not been to find a solution close to what I need as most solutions are often to use values e.g 'X', 'Y', 'Z' (in my example table) as table headers but I want to ideally be able to specify name for the table headers holding the new columns (Hope this all makes sense and someone can help :-) )

推荐答案

有几种方法可以得到所需的结果.如果您希望将PIVOT值分成几列，则可以使用几种不同的方式对查询进行硬编码.

There are several ways that you can get the desired result. If you have a limited number of values that you want to PIVOT into columns, then you can hard-code the query a few different ways.

带有CASE的汇总功能:

select 
  person,
  max(case when seq = 1 then category end) Cat1,
  max(case when seq = 1 then activity end) Cat1_Act,
  max(case when seq = 2 then category end) Cat2,
  max(case when seq = 2 then activity end) Cat2_Act,
  max(case when seq = 3 then category end) Cat3,
  max(case when seq = 3 then activity end) Cat3_Act
from
(
  select person, category, activity,
    row_number() over(partition by person
                      order by category) seq
  from yourtable
) d
group by person;

请参见带演示的SQL提琴.通过为每个用户的每个类别分配一个序列或row_number，您可以使用此行号将行转换为列.

See SQL Fiddle with Demo. By assigning a sequence or row_number to each category per user, you can use this row number to convert the rows into columns.

静态PIVOT:

如果要应用PIVOT函数，则我建议先将category和activity列取消透视成多行，然后再应用数据透视功能.

If you want to apply the PIVOT function, then I would first suggest unpivoting the category and activity columns into multiple rows and then apply the pivot function.

;with cte as
(
  select person, category, activity,
    row_number() over(partition by person
                      order by category) seq
  from yourtable
)
select person,
  cat1, cat1_act, 
  cat2, cat2_act,
  cat3, cat3_act
from
(
  select t.person, 
    col = case 
     when c.col = 'cat' then col+cast(seq as varchar(10))
      else 'cat'+cast(seq as varchar(10))+'_'+col
    end,
    value
  from cte t
  cross apply
 (
    select 'cat', category union all
    select 'act', activity
  ) c (col, value)
) d
pivot
(
  max(value)
  for col in (cat1, cat1_act, cat2, cat2_act,
              cat3, cat3_act)
) piv;

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

动态PIVOT::最后，如果值的数量未知，则可以使用动态SQL来获取结果:

Dynamic PIVOT: Finally if you have an unknown number of values then you can use dynamic SQL to get the result:

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

select @cols = STUFF((SELECT ',' 
                          + QUOTENAME(case 
                                       when d.col = 'cat' then col+cast(seq as varchar(10))
                                        else 'cat'+cast(seq as varchar(10))+'_'+col end) 
                    from 
                    (
                      select row_number() over(partition by person
                                                order by category) seq
                      from yourtable
                    ) t
                    cross apply
                    (
                      select 'cat', 1 union all
                      select 'act', 2
                    ) d (col, so)
                    group by col, so, seq
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT person, ' + @cols + ' 
            from 
            (
              select t.person, 
                col = case 
                 when c.col = ''cat'' then col+cast(seq as varchar(10))
                  else ''cat''+cast(seq as varchar(10))+''_''+col
                end,
                value
              from 
              (
                select person, category, activity,
                  row_number() over(partition by person
                                    order by category) seq
                from yourtable
              ) t
              cross apply
             (
                select ''cat'', category union all
                select ''act'', activity
              ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

请参见带有演示的SQL小提琴.所有版本都给出结果:

See SQL Fiddle with Demo. All versions give a result:

|    PERSON | CAT1 | CAT1_ACT | CAT2 | CAT2_ACT | CAT3 | CAT3_ACT |
| Username1 |    X |       X1 |    Y |       Y1 |    Z |       Z1 |

这篇关于将多行转换为具有指定标题的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！