SQL Server中的动态数据透视表 [英] Dynamic Pivot Table in 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小提琴

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

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小提琴

这篇关于SQL Server中的动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！