SQL Server 中的动态数据透视表

2022-01-22 00:00:00 pivot sql sql-server

您好，我有下表，我想将 EcoYear 旋转到顶部，但没有固定的年份，这些年份可以随时开始.此外，不同的案例会有不同的起始年份，所以我需要它填充 0 而不是 null.

Hello I have the following table and I want to pivot the EcoYear to be across the top but there aren't a set amount of years and the years could start anytime. In addition, different cases will have different starting years so I need it to pad 0 instead of null. 

CaseID EcoYear NetInv NetOil NetGas
38755   2006   123     2154         525 
38755   2007   123     2154         525 
38755   2008   123     2154         525 
38755   2009   123     2154         525 
38755   2010   123     2154         525 
38755   2011   123     2154         525 
38755   2012   123     2154         525 
38755   2013   123     2154         525 
38755   2014   123     2154         525 
38755   2015   123     2154         525 
38755   2016   123     2154         525 
38755   2017   123     2154         525 
38755   2018   123     2154         525 
38755   2019   123     2154         525 
38755   2020   123     2154         525

我需要表格如下所示:

I need the table to look like this:

CaseID Item 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 
38755 NetInv
38755 NetOil
38755 NetGas

这最初是通过 Access 使用交叉表完成的.

This was originally done with Access using a crosstab.

推荐答案

这可以在 sql server 中使用 UNPIVOT 和 PIVOT 来完成.静态版本是您知道要转换的列的地方:

This can be done in sql server using both an UNPIVOT and then a PIVOT. A Static version is where you know the columns to transform:

select *
from 
(
  select CaseId, EcoYear, val, item
  from yourtable
  unpivot
  (
    val
    for Item in (NetInv, NetOil, NetGas)
  )u
) x
pivot
(
  max(val)
  for ecoyear in ([2006], [2007], [2008], [2009], [2010], [2011],
                 [2012], [2013], [2014], [2015], [2016], [2017],
                 [2018], [2019], [2020])
) p

请参阅 SQL Fiddle with Demo

动态版本将确定执行记录:

A Dynamic Version will determine the records on execution:

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

select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(EcoYear) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

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


set @query 
  = 'select *
      from
      (
        select caseid, ecoyear, val, col
        from yourtable
        unpivot
        (
          val
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        max(val)
        for ecoyear in ('+ @colspivot +')
      ) p'

exec(@query)

请参阅 SQL Fiddle with Demo

相关文章