将行转置为SQL Server 2008 R2中的列 [英] Transpose rows into columns in SQL Server 2008 R2

问题描述

我该如何转动:

和这个:

对此:

在SQL Server 2008 R2中?

in SQL Server 2008 R2?

推荐答案

此问题与此

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.

由于要聚合字符串值，因此将需要应用min()或max()聚合函数，但是为了使最终结果显示多于一行，您需要使用某些内容来强制多行.

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.

为此，您将要使用row_number()为name中的每个parameter生成唯一的序列号.当您应用PIVOT功能时，此数字将在分组中使用，并且您将生成多行:

In order to do this you will want to use row_number() to generate a unique sequence number for each parameter in the name. When you apply the PIVOT function, this number will be used in the grouping and you will generate multiple rows:

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;

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

这也可以使用带有CASE表达式的聚合函数来编写:

This could also be written using an aggregate function with a CASE expression:

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;

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

如果您具有已知数量的名称，那么上述两个版本非常有用，那么您可以对查询进行硬编码，但是如果没有，则必须使用动态SQL:

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:

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;

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

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

|    CAR |            TRUCK |    BICYCLE |
|--------|------------------|------------|
|   make |          maxload |      frame |
|   year | hasconcretemixer |     isroad |
| engine |           (null) | ismountain |

这篇关于将行转置为SQL Server 2008 R2中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！