SQL将行转置为列(按键变量分组)? [英] SQL Transpose rows to columns (group by key variable)?
问题描述
我正在尝试将行转置为列，并按唯一标识符(CASE_ID)分组.
I am trying to transpose rows into columns, grouping by a unique identifier (CASE_ID).
我有一个具有以下结构的表:
I have a table with this structure:
CASE_ID AMOUNT TYPE
100 10 A
100 50 B
100 75 A
200 33 B
200 10 C
我正在尝试查询它以产生此结构...
And I am trying to query it to produce this structure...
| CASE_ID | AMOUNT1 | TYPE1 | AMOUNT2 | TYPE2 | AMOUNT3 | TYPE3 |
|---------|---------|-------|---------|-------|---------|--------|
| 100 | 10 | A | 50 | B | 75 | A |
| 200 | 33 | B | 10 | C | (null) | (null) |
(假定更大的数据集，其中包含CASE_ID，TYPE和AMOUNT的可能值)
(assume much larger dataset with large number of possible values for CASE_ID, TYPE and AMOUNT)
我尝试使用数据透视表，但不需要聚合函数(只需尝试重组数据).现在，我尝试以某种方式使用 row_number ，但不确定如何使用.
I tried to use pivot but I don't need an aggregate function (simply trying to restructure the data). Now I'm trying to somehow use row_number but not sure how.
我基本上是在尝试复制和调用名为Casestovars的SPSS命令，但是需要能够在SQL中完成.谢谢.
I'm basically trying to replicate and SPSS command called Casestovars, but need to be able to do it in SQL. thanks.
推荐答案
您可以通过使用row_number()创建一个序列号，然后使用带有CASE表达式的聚合函数来获得结果:
You can get the result by creating a sequential number with row_number() and then use an aggregate function with CASE expression:
select case_id,
max(case when seq = 1 then amount end) amount1,
max(case when seq = 1 then type end) type1,
max(case when seq = 2 then amount end) amount2,
max(case when seq = 2 then type end) type2,
max(case when seq = 3 then amount end) amount3,
max(case when seq = 3 then type end) type3
from
(
select case_id, amount, type,
row_number() over(partition by case_id
order by case_id) seq
from yourtable
) d
group by case_id;
请参见带演示的SQL提琴.
如果您正在使用具有PIVOT功能的数据库产品，则可以将row_number()与PIVOT一起使用，但是首先我建议您先取消amount和type列. SQL Server中有限数量的值的基本语法为:
If you are using a database product that has the PIVOT function, then you can use row_number() with PIVOT, but first I would suggest that you unpivot the amount and type columns first. The basic syntax for a limited number of values in SQL Server would be:
select case_id, amount1, type1, amount2, type2, amount3, type3
from
(
select case_id, col+cast(seq as varchar(10)) as col, value
from
(
select case_id, amount, type,
row_number() over(partition by case_id
order by case_id) seq
from yourtable
) d
cross apply
(
select 'amount', cast(amount as varchar(20)) union all
select 'type', type
) c (col, value)
) src
pivot
(
max(value)
for col in (amount1, type1, amount2, type2, amount3, type3)
) piv;
请参见带有演示的SQL小提琴.
如果值的数量未知，则可以使用动态SQL获取结果-SQL Server语法为:
If you have an unknown number of values, then you can use dynamic SQL to get the result - SQL Server syntax would be:
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 case_id
order by case_id) seq
from yourtable
) d
cross apply
(
select 'amount', 1 union all
select 'type', 2
) c (col, so)
group by col, so
order by seq, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT case_id,' + @cols + '
from
(
select case_id, col+cast(seq as varchar(10)) as col, value
from
(
select case_id, amount, type,
row_number() over(partition by case_id
order by case_id) seq
from yourtable
) d
cross apply
(
select ''amount'', cast(amount as varchar(20)) union all
select ''type'', type
) c (col, value)
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute sp_executesql @query;
请参见带演示的SQL小提琴.每个版本都会给出结果:
See SQL Fiddle with Demo. Each version will give the result:
| CASE_ID | AMOUNT1 | TYPE1 | AMOUNT2 | TYPE2 | AMOUNT3 | TYPE3 |
|---------|---------|-------|---------|-------|---------|--------|
| 100 | 10 | A | 50 | B | 75 | A |
| 200 | 33 | B | 10 | C | (null) | (null) |
这篇关于SQL将行转置为列(按键变量分组)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
