Home > SQL Tips > DYNAMIC PIVOT

DYNAMIC PIVOT


My previous PIVOT UNPIVOT post describes to organize data in a cross tab fashion or to transpose columns into rows and vice-versa.

This is fine only when you know how many columns you need. But when columns vary according to the query then how could you apply them on your scripts. The only way is to store them in a string variable at runtime and apply them in a dynamic SQL query, shown below.

This problem was also discussed on MSDN’s following link: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/26f86fd6-6d06-4cc5-9723-9ee4685bd48a where I proposed an answer also described below:

USE [tempdb]
GO

-- Create test tables
create table table1 (number int, desc varchar(20),
location int, numberatlocation int)
create table table2 (code int, name varchar(20))

-- Insert test data
insert into table1 values (12345,'test',1000,5)
insert into table1 values (12345,'test',1001,2)
insert into table1 values (12345,'test',1002,4)
insert into table1 values (12345,'test',1003,9)
insert into table1 values (12345,'test',1004,7)

insert into table2 values (1000,'loc1')
insert into table2 values (1001,'loc2')
insert into table2 values (1002,'loc3')
insert into table2 values (1003,'loc4')
insert into table2 values (1004,'loc5')

-- Static PIVOT
select number, description, [loc1], [loc2], [loc3], [loc4], [loc5]
from (select number, desc, numberatlocation, name
from table1 join table2 on table1.location=table2.code)p
PIVOT(MAX (numberatlocation) FOR Name IN ( [loc1], [loc2], [loc3], [loc4], [loc5] )
) AS pvt
ORDER BY number
Output of Static query:
number	desc	loc1	loc2	loc3	loc4	loc5
12345	test    5       2       4       9       7
-- Dynamic PIVOT

-- Lets add one more record on both the tables to check the results
insert into table1 values (12345,'test',1005,3)
insert into table2 values (1005,'loc6')

declare @col varchar(1000)
declare @sql varchar(2000)

select @col = COALESCE(@col + ', ','') + QUOTENAME(name)
from table2

select @col -- This gives: [loc1], [loc2], [loc3], [loc4], [loc5], [loc6]

-- Now setting this @col variable in the Dynamic SQL.
set @sql = '
select number, desc, ' + @col + '
from (select number, desc, numberatlocation, name
from table1 join table2 on table1.location=table2.code)p
PIVOT(MAX (numberatlocation) FOR Name IN ( ' + @col + ' )
) AS pvt
ORDER BY number'

print @sql

exec (@sql)
Output of Dynamic query:
number	desc	loc1	loc2	loc3	loc4	loc5    loc6
12345	test    5       2       4       9       7       3
-- Final Cleanup
drop table table1
drop table table2
About these ads
  1. September 25, 2011 at 4:53 pm

    The following code includes a small correction to it has more than one line should be back at the results.

    select @col = COALESCE(@col + ‘, ‘,”) + QUOTENAME(name)
    –from DynamicPivotTbl02
    from (select distinct name from DynamicPivotTbl02) Tbl
    select @col

    * Try adding the following records to see the problem existing code and replace the line of the patch and get good results

    insert into DynamicPivotTbl01 values (1234,’test’,100,5)
    insert into DynamicPivotTbl01 values (1234,’test’,101,2)
    insert into DynamicPivotTbl01 values (1234,’test’,102,4)
    insert into DynamicPivotTbl01 values (1234,’test’,103,9)
    insert into DynamicPivotTbl01 values (1234,’test’,104,7)
    insert into DynamicPivotTbl01 values (1234,’test’,105,3)

    insert into DynamicPivotTbl02 values (100,’loc1′)
    insert into DynamicPivotTbl02 values (101,’loc2′)
    insert into DynamicPivotTbl02 values (102,’loc3′)
    insert into DynamicPivotTbl02 values (103,’loc4′)
    insert into DynamicPivotTbl02 values (104,’loc5′)
    insert into DynamicPivotTbl02 values (105,’loc6′)

  2. Maurik
    January 4, 2012 at 9:55 am

    Hi! Pretty cool!!!!

    Could you explain how could I save the results of the pivot into a temporary table?

    Thanks very much!! Maurik

    • January 4, 2012 at 10:11 am

      Just add INTO #tempTable below the SELECT statement (1st line) of the PIVOT query.

  3. Maurik
    January 4, 2012 at 1:37 pm

    Thank you very much.

    I am sorry to bother you again, but I cannot seem to get it right. This is what I have now:

    declare @col varchar(8000)
    declare @sql varchar(8000)

    select @col = COALESCE(@col + ‘, ‘,”) + QUOTENAME(mrknaam)
    from #mrk
    order by mrknaam asc

    — Now setting this @col variable in the Dynamic SQL.
    set @sql = ‘select patid, ‘ + @col + ‘
    into #tempTable
    from (select patid , mrknaam , sumfact
    from #period1) as p
    PIVOT
    (SUM (sumfact) FOR mrknaam IN ( ‘ + @col + ‘ )
    )AS pvt1
    ORDER BY patid’

    print @sql

    exec (@sql)

    Now, if I select * from #tempTable (either as a new select statement or from withing the @sql string, I get an error message: invalid object name?

    Thanks very much! Maurik

    • January 5, 2012 at 8:22 am

      You know, this is not possible with temp-table as the temp table is created in different scope/context, the EXEC() one, which cannot be referenced at all.

      So, try using global temp-table instead, like:

      into ##tempTable

  4. Maurik
    January 5, 2012 at 9:50 am

    Thanks a bunch! You rock! :-) I hadn’t figured out the different scope part at all. It works like a charm now.

  5. David
    January 17, 2012 at 2:18 pm

    Very useful – the solution to the exact problem I was having!

  6. Debbie
    February 3, 2012 at 11:52 am

    Hi I have the following code that I am needing to replace the null values with 0
    declare @col varchar(1000)
    declare @sql varchar(2000)

    select @col = COALESCE(@col + ‘, ‘,”) + QUOTENAME(FiscalYear)
    from (select distinct FiscalYear from ##T2)t

    set @sql = ‘
    select ClientNumber, ClientName, WorkType,’ + @col + ‘
    into ##T3
    from (select ClientNumber, ClientName,WorkType,BillFees,FiscalYear from ##T2)p
    PIVOT(Sum(BillFees) FOR FiscalYear IN ( ‘ + @col + ‘ )
    )AS pvt
    ORDER BY WorkType,ClientNumber’

    exec (@sql)

    (EXCUSE ALIGNMENT)
    RESULT SET
    ClientName WorkType 2010 2011
    A D-1 1 NULL
    B D-2 NULL 2
    C D-3 NULL 3
    A D-4 2 3
    B D-5 2 3
    C D-6 NULL 2
    A D-7 23 3
    B D-8 NULL 3
    C D-9 3 3
    A D-10 NULL 3

    Please could you assist with where I should place the isnull(,0) or how I sould approach this?

    • February 3, 2012 at 12:13 pm

      Check this:

      declare @col varchar(1000)
      declare @col2 varchar(1000)
      declare @sql varchar(2000)

      select @col = COALESCE(@col + ‘, ‘,”) + ‘ISNULL(‘+FiscalYear+’,0) AS ‘+QUOTENAME(FiscalYear)
      from (select distinct FiscalYear from ##T2)t

      select @col2 = COALESCE(@col2 + ‘, ‘,”) + QUOTENAME(FiscalYear)
      from (select distinct FiscalYear from ##T2)t

      set @sql = ‘
      select ClientNumber, ClientName, WorkType,’ + @col + ‘
      into ##T3
      from (select ClientNumber, ClientName,WorkType,BillFees,FiscalYear from ##T2)p
      PIVOT(Sum(BillFees) FOR FiscalYear IN ( ‘ + @col2 + ‘ )
      )AS pvt
      ORDER BY WorkType,ClientNumber’

      exec (@sql)

  7. Debbie
    February 3, 2012 at 1:27 pm

    get the error below
    Msg 245, Level 16, State 1, Line 209
    Conversion failed when converting the varchar value ‘,0) AS ‘ to data type int.

  8. Debbie
    February 6, 2012 at 9:16 am

    Fixed it!
    declare @col varchar(1000)
    declare @col2 varchar(1000)
    declare @sql varchar(2000)

    select @col = COALESCE(@col + ‘, ‘,”) + ‘ISNULL(‘+QUOTENAME(FiscalYear)+’,0) AS ‘+QUOTENAME(FiscalYear)
    from (select distinct FiscalYear from ##T2)t

    select @col2 = COALESCE(@col2 + ‘, ‘,”) + QUOTENAME(FiscalYear)
    from (select distinct FiscalYear from ##T2)t

    set @sql = ‘
    select ClientNumber, ClientName, WorkType,’ + @col + ‘
    into ##T3
    from (select ClientNumber, ClientName,WorkType,BillFees,FiscalYear from ##T2)p
    PIVOT(Sum(BillFees) FOR FiscalYear IN ( ‘ + @col2 + ‘ )
    )AS pvt
    ORDER BY WorkType,ClientNumber’

    exec (@sql)

    the +QUOTENAME needed to be in front of the FiscalYear in @col or it couldn’t read the line poperly.

    D

  9. Debbie
    February 6, 2012 at 9:58 am

    Thank you for your assistance

    • February 6, 2012 at 10:11 am

      Hey Debbie, thanks for your comments.

      Sorry to get back you so late, its good that you resolved it yourself.

      happy reading !!!

  10. Debbie
    February 7, 2012 at 10:36 am

    Hi there, just in case anyone comes across this, when trying to use the above cod in a stored procedure you wll get an “invalid Object Name ##[Name of your Gobal temp Table]

    this is because ssrs is tryig to parse the entire content of the stored procedure and cannot find where the ##table is created in the code. I got around this by creating the ##Table prior to exec@sql as shown below. This code will however only work if the number of dynamic columns can be determined before the code is executed. You could also add some code to get a count of the dynamically created columns and then add them to the table that way.

    declare @col varchar(1000)
    declare @col2 varchar(1000)
    declare @sql varchar(2000)

    select @col = COALESCE(@col + ‘, ‘,”) + ‘ISNULL(‘ + QUOTENAME(FiscalYear)+ ‘,0) AS ‘+QUOTENAME(FiscalYear)
    from (select distinct FiscalYear from ##T2)t

    select @col2 = COALESCE(@col2 + ‘, ‘,”) + QUOTENAME(FiscalYear)
    from (select distinct FiscalYear from ##T2)t

    create table ##T3
    (ClientNumber varchar(60)
    ,ClientName Varchar (250)
    ,WorkType varchar(10)
    ,A decimal(16,2)
    ,B decimal (16,2)
    )

    set @sql = ‘insert into ##T3
    select ClientNumber, ClientName, WorkType,’ + @col + ‘
    from (select ClientNumber, ClientName,WorkType,BillFees,FiscalYear from ##T2)p
    PIVOT(Sum(BillFees) FOR FiscalYear IN ( ‘ + @col2 + ‘ )
    )AS pvt
    ORDER BY WorkType,ClientNumber’

    exec (@sql)

    create table #T4
    (ClientNumber varchar(60)
    ,ClientName Varchar (250)
    ,WorkType varchar(10)
    ,PriorYear decimal(16,2)
    ,CurrentYear decimal (16,2)
    ,Dif Decimal (16,2)
    ,per decimal(16,2)
    )

    insert into #T4 (ClientNumber,ClientName,WorkType,PriorYear,CurrentYear)
    select * from ##T3

  11. Will
    February 28, 2012 at 9:01 am

    I like your article, it has helped me a lot. One question though. Suppose in your example you want the column names returned from the dynamic sql to be static regardless of what the values are that are returned from table2. For example, I would like the value of @col to be: [loc1] as Month6, [loc2] as Month5, [loc3] as Month4, [loc4] as Month3, [loc5] as Month2, [loc6] as Month1. Have you any thoughts about how this can be achieved?
    Thanks

  12. shaily Shah
    July 5, 2012 at 5:21 pm

    Please see this.
    My Records are in the following form,

    ClientNo FeeAmount FeeDec
    —————————————————– ————————————————————–E9429 6300.00 Education
    E9429 4500.00 Transportation
    E9421 7100.00 Education
    E9421 4500.00 Transportation
    G9443 6750.00 Education
    G9442 6900.00 Education
    G9443 4500.00 Transportation
    G9442 4500.00 Transportation
    E9447 6300.00 Education
    E9447 4500.00 Transportation

    I Wan the Formated as Under.

    ClientNo Education Transportation
    ———————————————————————————————————–
    E9429 6300 4500
    E9421 7100 4500
    G9443 6750 4500

    What I Should Change in the Below Code for the above result???

    As I Am using Common Table Expression for two Different tables by reviewing ur solution.

    I Tried this but its not working…

    Thanks in Advance

    ;with FIRSTCTE
    AS
    (
    SELECT
    DISTINCT
    A.ReceiptNo AS ReceiptNo,
    Cl.No AS ClientNo,
    A.FeeAmount AS FeeAmount
    FROM
    (
    SELECT
    T.Id AS ReceiptNo,
    SUM(TL.Amount) AS FeeAmount
    FROM
    dbo.[Transaction] T
    INNER JOIN dbo.TransactionLineItem TL
    ON
    (
    TL.TransactionId = T.Id
    )
    GROUP BY
    T.Id
    )A
    INNER JOIN dbo.[Transaction] T
    ON
    (
    T.Id = A.ReceiptNo
    )
    INNER JOIN dbo.TransactionLineItem TLI
    ON
    (
    TLI.TransactionId = T.Id
    )
    INNER JOIN dbo.BusinessEntity BE
    ON
    (
    BE.Id= t.BusinessEntityId
    )
    INNER JOIN dbo.Client CL
    ON
    (
    Cl.BusinessEntityId=BE.Id
    )
    WHERE
    TLI.PeriodId=11

    ),

    SECONDCTE
    AS
    (
    SELECT
    DISTINCT
    T.Id AS ReceiptNo,
    RE.Description AS FeeDec
    FROM
    dbo.[Transaction] T
    INNER JOIN dbo.TransactionLineItem TLI
    ON
    (
    TLI.TransactionId = T.Id
    )
    INNER JOIN dbo.Fee F
    ON
    (
    F.Id = TLI.FeeId
    )
    INNER JOIN ReferenceData RE
    ON
    (
    RE.Id = F.FeeTypeId
    )
    WHERE
    TLI.PeriodId=11

    )

    SELECT
    ClientNo,
    [Edu],
    [Tra]
    FROM
    (
    SELECT
    F.ClientNo AS ClientNo,
    F.FeeAmount AS FeeAmount,
    S.FeeDec AS FeeDec
    FROM
    FIRSTCTE F
    JOIN SECONDCTE S
    ON
    F.ReceiptNo = S.ReceiptNo
    )P
    PIVOT
    (
    MAX(FeeAmount) FOR FeeDec IN([Edu],[Tra])
    )AS PVt

    • Brian H. Lee
      November 27, 2012 at 1:29 pm

      Would be helpful if we saw a sample of each table that you are using to create the PIVOT table.

  13. Wietse
    August 16, 2012 at 3:59 pm

    Wil this work for UNPIVOT as well, when the column names are dynamic? It would be ideal for my application if it can be done in a view.

  14. Jason Verhaeghe
    August 21, 2012 at 1:05 pm

    Hi there,
    This really helped me a lot. It’s a pity that MS doesn’t provide samples like these. Pivoting is a very common practice in reporting.

  15. Tracy Hamlin
    August 28, 2012 at 9:14 pm

    Thank you! Other posts I was reading were way too confusing and weren’t dynamic enough, either. This is perfect :)

  1. November 6, 2013 at 6:10 pm
  2. July 5, 2014 at 10:54 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 396 other followers

%d bloggers like this: