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

问题描述

我该如何转这个:

还有这个:

进入这个:

在 SQL Server 2008 R2 中?

in SQL Server 2008 R2?

推荐答案

这个问题和这个问题很相似PIVOT 行到返回超过 1 个值的列，您需要将行中的字符串数据聚合到列中.我将修改该答案以演示如何将数据转换为最终结果.

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 Fiddle with Demo.

这也可以使用带有 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 Fiddle with Demo.

如果您有已知数量的名称，上述两个版本非常棒，那么您可以对查询进行硬编码，但如果您没有，那么您将不得不使用动态 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 Fiddle with Demo.所有版本都会给出结果:

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屋！