T-SQL中的多列数据透视

I am working with a table where there are multiple rows that I need pivoted into columns. So the pivot is the perfect solution for this, and works well when all I need is one field. I am needing to return several fields based upon the pivot. Here is the pseudo code with specifics stripped out:

我正在使用一个表，其中有多行需要转换为列。所以枢轴是这个的完美解决方案，并且当我需要的只是一个领域时效果很好。我需要根据枢轴返回几个字段。这是伪代码，其中删除了详细信息：

SELECT 
  field1,
  [1], [2], [3], [4]
FROM
  (
  SELECT 
    field1, 
    field2, 
    (ROW_NUMBER() OVER(PARTITION BY field1 ORDER BY field2)) RowID
  FROM tblname
  ) AS SourceTable
PIVOT
  (
  MAX(field2)
  FOR RowID IN ([1], [2], [3], [4])
  ) AS PivotTable;

The above syntax works brilliantly, but what do I do when I need to get additional information found in field3, field4....?

上面的语法非常出色，但是当我需要获取field3，field4中的其他信息时，我该怎么办？

5 个解决方案

#1

Rewrite using MAX(CASE...) and GROUP BY:

使用MAX（CASE ...）和GROUP BY重写：

select 
  field1
, [1] = max(case when RowID = 1 then field2 end)
, [2] = max(case when RowID = 2 then field2 end)
, [3] = max(case when RowID = 3 then field2 end)
, [4] = max(case when RowID = 4 then field2 end)
from (
  select 
    field1
  , field2
  , RowID = row_number() over (partition by field1 order by field2)
  from tblname
  ) SourceTable
group by 
  field1

From there you can add in field3, field4, etc.

从那里你可以添加field3，field4等。

#2

I am unsure if you are using MS SQL Server, but if you are... You may want to take a look at the CROSS APPLY functionality of the engine. Basically it will allow you to apply the results of a table-valued UDF to a result set. This would require you to put your pivot query into a table-valued result set.

我不确定您是否使用MS SQL Server，但如果您是...您可能需要查看引擎的CROSS APPLY功能。基本上，它允许您将表值UDF的结果应用于结果集。这将要求您将数据透视查询放入表值结果集中。

http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx

#3

wrap your sql statement with something like:

用以下内容包装你的sql语句：

select a.segment, sum(field2), sum(field3) 
from (original select with case arguments) a
group by a.segment

It should collapse your results into one row, grouped on field1.

它应该将结果折叠成一行，并在field1上分组。

#4

The trick to doing multiple pivots over a row_number is to modify that row number sequence to store both the sequence and the field number. Here's an example that does what you want with multiple PIVOT statements.

在row_number上执行多个枢轴的技巧是修改该行号序列以存储序列和字段编号。这是一个使用多个PIVOT语句执行所需操作的示例。

-- populate some test data
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (
    ID int identity(1,1) not null,
    MainField varchar(100),
    ThatField int,
    ThatOtherField datetime
)

insert into #tmp (MainField, ThatField, ThatOtherField)
select 'A', 10, '1/1/2000' union all
select 'A', 20, '2/1/2000' union all
select 'A', 30, '3/1/2000' union all
select 'B', 10, '1/1/2001' union all
select 'B', 20, '2/1/2001' union all
select 'B', 30, '3/1/2001' union all
select 'B', 40, '4/1/2001' union all
select 'C', 10, '1/1/2002' union all
select 'D', 10, '1/1/2000' union all
select 'D', 20, '2/1/2000' --union all

-- pivot over multiple columns using the 1.1, 1.2, 2.1, 2.2 sequence trick
select
    MainField,
    max([1.1]) as ThatField1,
    max([1.2]) as ThatOtherField1,
    max([2.1]) as ThatField2,
    max([2.2]) as ThatOtherField2,
    max([3.1]) as ThatField3,
    max([3.2]) as ThatOtherField3,
    max([4.1]) as ThatField4,
    max([4.2]) as ThatOtherField4
from
    (
        select x.*,
            cast(row_number() over (partition by MainField order by ThatField) as varchar(2)) + '.1' as ThatFieldSequence,
            cast(row_number() over (partition by MainField order by ThatField) as varchar(2)) + '.2' as ThatOtherFieldSequence
        from #tmp x
    ) a
    pivot (
        max(ThatField) for ThatFieldSequence in ([1.1], [2.1], [3.1], [4.1])
    ) p1
    pivot (
        max(ThatOtherField) for ThatOtherFieldSequence in ([1.2], [2.2], [3.2], [4.2])
    ) p2
group by
    MainField

#5

It is possible to pivot on multiple columns, but you need to be careful about reusing the pivot column across multiple pivots. Here is a good blog post on the subject:

可以在多个列上进行旋转，但是需要注意在多个枢轴上重用枢轴列。这是一篇关于这个主题的好博文：

http://pratchev.blogspot.com/2009/01/pivoting-on-multiple-columns.html

#1