Dynamic Pivot Table in SQL Server

Question

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

This was originally done with Access using a crosstab.

Short answer - Don't do this in SQL Server, do it in whatever application you're using to present the data. — Steve Mayne, Sep 14 '12 at 19:07
My task is to do it in SQL Server because the developers cannot use Access in the new version. Thanks! — Roland P, Sep 14 '12 at 19:16
A search for "dynamic sql" "Pivot" should show you how to do it. Try it, and come back with any issues — podiluska, Sep 14 '12 at 19:20
possible duplicate of [Simple way to transpose columns and rows in Sql?](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — RichardTheKiwi, May 03 '13 at 09:54

Taryn · Accepted Answer · 2013-05-17T17:58:33.527

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

see 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)

see SQL Fiddle with Demo

Thank you. This is exactly what I needed to do. Will this dynamic version be able to add additional columns for other cases that might start at a later date than 2006? For example if it started in 2008 it would go out to 2022 but for the columns with 2006 and 2007 will just show zeros. And thank you for your help. I will use this as a basis for my other reports! — Roland P, Sep 14 '12 at 19:51
@RolandP it will add columns for whatever fields you have in your `EcoYear`, you can then restrict the years as needed. — Taryn, Sep 14 '12 at 19:54

Dynamic Pivot Table in SQL Server

1 Answers1

Linked

Related