SQL Server中的动态数据透视表

Question

你好，我有以下表格需要进行数据透视。我想将EcoYear（经济年度）作为列的表头，但是年数并不固定，且可能从任何时间开始。此外，不同的情况将有不同的起始年份，因此我需要补0而不是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 

我需要表格长成这样：

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

最初使用交叉表方法在Access中完成此操作。

- Roland P

---

简短回答 - 不要在SQL Server中这样做，而是在你用来呈现数据的应用程序中完成。 - Steve Mayne

我的任务是在SQL Server中完成它，因为开发人员无法在新版本中使用Access。谢谢！ - Roland P

搜索“动态SQL”和“Pivot”，你就会知道如何做。试一试，如果有任何问题，请回来告诉我。 - podiluska

可能是在Sql中转置列和行的简单方法？的重复问题。 - RichardTheKiwi

Answer 1

可以使用 UNPIVOT 和 PIVOT 在 SQL Server 中完成这个操作。如果你知道需要转换的列，那么可以使用固定版本的 PIVOT：

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演示案例

动态版本将在执行时确定记录：

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

- Taryn

---

谢谢。这正是我需要做的。这个动态版本能否添加其他情况的额外列，这些情况可能比2006年晚开始？例如，如果它从2008年开始，它将延伸到2022年，但对于2006年和2007年的列，将只显示零。非常感谢您的帮助。我将以此为基础制作我的其他报告！ - Roland P

@RolandP 它会根据您在“EcoYear”中拥有的任何字段添加列，然后根据需要限制年份。 - Taryn