TSQL question here. See source and desired output in image below. Code to build source table is also provided.
这里有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)
3 个解决方案
#1
5
Since you want to PIVOT two columns of data, one way you can do this would be to apply both the UNPIVOT and the PIVOT functions. The UNPIVOT will convert the multiple columns category
and value
to multiple rows, then you can apply the PIVOT to get the final result:
由于你想要PIVOT两列数据,你可以这样做的一种方法是同时应用UNPIVOT和PIVOT函数。 UNPIVOT会将多列类别和值转换为多行,然后您可以应用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;
See SQL Fiddle with Demo.
请参阅SQL Fiddle with Demo。
If you have an unknown number of values, then you will have to use dynamic SQL similar to this:
如果您具有未知数量的值,则必须使用与此类似的动态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
#2
2
This being the first time I've used PIVOT, the code is probably pretty ugly. Here goes:
这是我第一次使用PIVOT,代码可能非常难看。开始:
with ranked as (
select *, RANK() OVER (PARTITION by record ORDER by category) as r
from @tablevar
), labeled as (
select record, category as content, 'category' + CAST(r as varchar(MAX)) as label
from ranked
union all
select record, cast(value AS nvarchar(MAX)), 'value' + CAST(r as varchar(MAX)) as label
from ranked) --select * from labeled
select record, [category1] as [category], [value1] as [value], [category2] as [category], [value2] as [value], [category3] as [category], [value3] as [value]
from (SELECT * FROM labeled) as source
PIVOT(
max(content)
for label in ([category1], [value1], [category2], [value2], [category3], [value3])) as pvt
#3
2
Here's my stab at it
这是我对它的刺痛
;with Z as
(
select record, category, value, ROW_NUMBER() over (partition by record order by category) as ranker
from @tablevar
)
select Z2.record, Z2.c1, Z3.v1, Z2.c2, Z3.v2, Z2.c3, Z3.v3 from
(
select record, [1] c1, [2] c2, [3] c3 from
(select record, category, ranker from Z) as Z0
pivot
( min(category) for ranker in ([1], [2], [3])) as pvt
) Z2
join
(
select record, [1] v1, [2] v2, [3] v3 from
(select record, value, ranker from Z) as Z1
pivot
( min(value) for ranker in ([1], [2], [3])) as pvt
) Z3
on Z2.record = Z3.record
#1
5
Since you want to PIVOT two columns of data, one way you can do this would be to apply both the UNPIVOT and the PIVOT functions. The UNPIVOT will convert the multiple columns category
and value
to multiple rows, then you can apply the PIVOT to get the final result:
由于你想要PIVOT两列数据,你可以这样做的一种方法是同时应用UNPIVOT和PIVOT函数。 UNPIVOT会将多列类别和值转换为多行,然后您可以应用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;
See SQL Fiddle with Demo.
请参阅SQL Fiddle with Demo。
If you have an unknown number of values, then you will have to use dynamic SQL similar to this:
如果您具有未知数量的值,则必须使用与此类似的动态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
#2
2
This being the first time I've used PIVOT, the code is probably pretty ugly. Here goes:
这是我第一次使用PIVOT,代码可能非常难看。开始:
with ranked as (
select *, RANK() OVER (PARTITION by record ORDER by category) as r
from @tablevar
), labeled as (
select record, category as content, 'category' + CAST(r as varchar(MAX)) as label
from ranked
union all
select record, cast(value AS nvarchar(MAX)), 'value' + CAST(r as varchar(MAX)) as label
from ranked) --select * from labeled
select record, [category1] as [category], [value1] as [value], [category2] as [category], [value2] as [value], [category3] as [category], [value3] as [value]
from (SELECT * FROM labeled) as source
PIVOT(
max(content)
for label in ([category1], [value1], [category2], [value2], [category3], [value3])) as pvt
#3
2
Here's my stab at it
这是我对它的刺痛
;with Z as
(
select record, category, value, ROW_NUMBER() over (partition by record order by category) as ranker
from @tablevar
)
select Z2.record, Z2.c1, Z3.v1, Z2.c2, Z3.v2, Z2.c3, Z3.v3 from
(
select record, [1] c1, [2] c2, [3] c3 from
(select record, category, ranker from Z) as Z0
pivot
( min(category) for ranker in ([1], [2], [3])) as pvt
) Z2
join
(
select record, [1] v1, [2] v2, [3] v3 from
(select record, value, ranker from Z) as Z1
pivot
( min(value) for ranker in ([1], [2], [3])) as pvt
) Z3
on Z2.record = Z3.record