问题描述
我有一个表格,其中包含个人进行的活动的详细信息-该表格的内容类似于以下内容:
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 |
这篇关于将多行转换为具有指定标题的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!