无需聚合即可转置行和列

问题描述

我有以下数据集

Date            Field1        Col1  Col2    Col3
2012/07/02      Customer1      CL   DS      RT
2012/07/03      Customer1      DS   RT      700
2012/07/04      Customer1      DS   RT      700
2012/07/02      Customer2      CL   DS      RT
2012/07/03      Customer2      DS   RT      1500
2012/07/04      Customer2      DS   RT      1500
2012/07/02      Customer3      CL   DS      RT
2012/07/03      Customer3      DS   RT      6000
2012/07/04      Customer3      DS   RT      6000
2012/07/02      Customer4      CL   RC      RT
2012/07/03      Customer4      RC   RT      4900
2012/07/04      Customer4      RC   RT      4900

我希望输出如下:

Field1  2012/07/02  2012/07/03  2012/07/04
Col1    Customer1   CL  DS  DS
Col2    Customer1   DS  RT  RT
Col3    Customer1   RT  700 700
Col1    Customer2   CL  DS  DS
Col2    Customer2   DS  RT  RT
Col3    Customer2   RT  1500    1500
Col1    Customer3   CL  DS  DS
Col2    Customer3   DS  RT  RT
Col3    Customer3   RT  6000    6000
Col1    Customer4   CL  RC  RC
Col2    Customer4   RC  RT  RT
Col3    Customer4   RT  4900    4900

问题还在于我有一个不固定数量的客户 (Field1) &日期数量不定.

The problem is also that I have an unfixed amount of Customers (Field1) & unfixed amount of Dates.

推荐答案

您尝试执行的操作称为 PIVOT.有两种方法可以完成这些，一种是使用静态数据透视，其中您对所有要转换的值进行硬编码，或者使用动态数据透视，其中值在执行时确定.

What you are trying to do is known as a PIVOT. There are two ways that these can be done, either with a static pivot where you hard-code all of the values to transform or a dynamic pivot where the values were determined at execution.

静态版本(参见SQL Fiddle with Demo):

select *
from
(
  select dt, field1, col, value
  from yourTable
  unpivot
  (
    value for col in (col1, col2, col3)
  ) u
)x1
pivot
(
  min(value)
  for dt in ([2012-07-02], [2012-07-03], [2012-07-04])
) p

Dynamic Pivot(参见 SQL Fiddle with Demo):

Dynamic Pivot (See SQL Fiddle with Demo):

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('yourTable') and
               C.name like 'col%'
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT distinct ', ' + QUOTENAME(convert(char(10), dt, 120))
                    from yourTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query =
          'SELECT col, field1, ' + +@colsPivot+ '
           FROM
           (
              select dt, field1, col, value
              from yourTable
              unpivot
              (
                value for col in ('+ @colsUnpivot+')
              ) u
           )x1
           pivot
           (
             min(value)
             for dt in ('+@colsPivot+')
           ) p '

exec(@query)

当您不知道需要转换为列的项目数时，动态枢轴是一个不错的选择.两者都会产生相同的结果.

The dynamic pivot is a great option when you do not know the number of items that you need to convert into columns. Both will yield the same results.

编辑#1，如果您想要特定顺序的日期列 - desc 等，那么您将使用以下内容来获取日期(请参阅 SQL Fiddle with Demo):

Edit #1, if you want the date columns in a specific order - desc, etc, then you will use the following to get the dates (See SQL Fiddle with Demo):

select @colsPivot = STUFF((SELECT ', ' + QUOTENAME(convert(char(10), dt, 120))
                    from yourTable
                    group by dt
                    ORDER by dt desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

这篇关于无需聚合即可转置行和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！