Geek asks and answers

Dynamic PivotTable in SQL Server

Hello, I have the following table, and I want EcoYear to be on top, but there is no specific number of years, and years can start anytime. In addition, different cases will have different starting years, so I need it to fill 0 instead of zero.

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

This was done using Access using a crosstab.

+5
source share
1 answer

This can be done on the sql server using both UNPIVOT, and PIVOT. The static version is where you know the columns to convert:

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

see SQL Fiddle with Demo

The dynamic version will determine the entries when executed:

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)

see SQL Fiddle with Demo

+14

More articles:


All Articles