在 SQL Server 2008 R2 中将行转换为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20119162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transpose rows into columns in SQL Server 2008 R2
提问by Bashir Magomedov
How do I turn this:
我如何转这个：
and this:
和这个：
into this:
进入这个：
in SQL Server 2008 R2?
在 SQL Server 2008 R2 中？
回答by Taryn
This question is very similar to this one PIVOT rows to columns with more than 1 value returned, where you need to aggregate string data from rows into columns. I will modify that answer to demonstrate how you can convert your data to your final result.
这个问题非常类似于这个PIVOT 行到返回值超过 1 的列，您需要将行中的字符串数据聚合到列中。我将修改该答案以演示如何将数据转换为最终结果。
Since you are aggregating string values, you will need to apply either the min()or max()aggregate function, but in order to get the final result to display more than one row you need something to force the multiple rows.
由于您正在聚合字符串值，因此您需要应用min()或max()聚合函数，但为了获得显示多行的最终结果，您需要一些东西来强制多行。
In order to do this you will want to use row_number()to generate a unique sequence number for each parameterin the name. When you apply the PIVOT function, this number will be used in the grouping and you will generate multiple rows:
为了做到这一点，你会想用row_number()生成每个唯一序列号parameter的name。当您应用 PIVOT 函数时，此数字将用于分组，您将生成多行：
select Car, Truck, Bicycle
from
(
select vt.name, vp.parameter,
row_number() over(partition by vt.name
order by vt.id) seq
from vehicle_types vt
left join vehicle_parameters vp
on vt.id = vp.vehicletype
) d
pivot
(
max(parameter)
for name in (Car, Truck, Bicycle)
) piv;
See SQL Fiddle with Demo.
This could also be written using an aggregate function with a CASE expression:
这也可以使用带有 CASE 表达式的聚合函数来编写：
select
max(case when name = 'Car' then parameter end) Car,
max(case when name = 'Truck' then parameter end) Truck,
max(case when name = 'Bicycle' then parameter end) Bicycle
from
(
select vt.name, vp.parameter,
row_number() over(partition by vt.name
order by vt.id) seq
from vehicle_types vt
left join vehicle_parameters vp
on vt.id = vp.vehicletype
) d
group by seq;
See SQL Fiddle with Demo.
The above two versions are great if you have a known number of names, then you can hard-code the query but if you don't then you will have to use dynamic SQL:
如果您有已知数量的名称，则上述两个版本都很棒，那么您可以对查询进行硬编码，但如果不这样做，则必须使用动态 SQL：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(name)
from vehicle_types
group by id, name
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + '
from
(
select vt.name, vp.parameter,
row_number() over(partition by vt.name
order by vt.id) seq
from vehicle_types vt
left join vehicle_parameters vp
on vt.id = vp.vehicletype
) x
pivot
(
max(parameter)
for name in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. All versions give a result:
请参阅SQL Fiddle with Demo。所有版本都给出了结果：
| CAR | TRUCK | BICYCLE |
|--------|------------------|------------|
| make | maxload | frame |
| year | hasconcretemixer | isroad |
| engine | (null) | ismountain |
