locked
Create a Pivot Table RRS feed

  • Question

  • I need to pivot a temp table but am having some trouble.  this is the temp table structure:

    CREATE TABLE #ColHeaders (ColName nvarchar(200), Caption nvarchar(200), Sort int)

    Below is a screenshot of a mockup showing the data in the temp table, and below that is what I want to pivot to produce.

    The row of data will be sent to an SSRS report and used to dynamically set the column headers in the report. This is the first step to globalizing the report.

    Thank you.

    Thursday, August 15, 2013 7:54 PM

Answers

  • DECLARE @pivot_cols nvarchar(max)
    SELECT @pivot_cols =      
            STUFF((SELECT DISTINCT '],[' + ColName       
                   FROM  #ColHeaders    
                   ORDER BY '],[' + ColName       
                   FOR XML PATH('')      
                   ), 1, 2, '') + '] '; 
                   
    print @pivot_cols
    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query=N'
    SELECT'+@pivot_cols+' 
    from
    (
      SELECT ColName, Caption,
        row_number() over(partition by sort order by sort asc) rn
      from #ColHeaders
    ) as st
    pivot
    (
      max(Caption)
      FOR ColName in ('+@pivot_cols+')
    ) as pivottable'
    EXEC(@pivot_query);

    Try the Above Code it'll dynamically creates the columns.
    • Marked as answer by moondaddy Friday, August 16, 2013 3:15 PM
    Friday, August 16, 2013 6:14 AM

All replies

  • I think this is a great reference for all things pertaining to Pivots in SQL Server.

    https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, August 15, 2013 8:37 PM
  • Try this,

    SELECT * FROM (SELECT ColName,Caption FROM #ColHeaders) X
    PIVOT
    (
     MAX(Caption) FOR ColName IN (Ls_Number,Ls_NumberAlt1,Ls_NumberAlt2,Ls_Lessor,Ls_LsRtTp_Id,Ls_LsTp_Id)
    ) PVT


    Regards, RSingh

    Friday, August 16, 2013 3:41 AM
  • Thanks to all, but keep in mind, you hard coded the columns.  this is the table definition we are getting the data from:

    CREATE TABLE #ColHeaders (ColName nvarchar(200), Caption nvarchar(200), Sort int)

    we don't know what the column names will be and we don't know what the captions will be.  to this must be a dynamic pivot table.  No columns names can be hard coded.

    Any more ideas?

    Thanks again.

    Friday, August 16, 2013 3:53 AM
  • DECLARE @pivot_cols nvarchar(max)
    SELECT @pivot_cols =      
            STUFF((SELECT DISTINCT '],[' + ColName       
                   FROM  #ColHeaders    
                   ORDER BY '],[' + ColName       
                   FOR XML PATH('')      
                   ), 1, 2, '') + '] '; 
                   
    print @pivot_cols
    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query=N'
    SELECT'+@pivot_cols+' 
    from
    (
      SELECT ColName, Caption,
        row_number() over(partition by sort order by sort asc) rn
      from #ColHeaders
    ) as st
    pivot
    (
      max(Caption)
      FOR ColName in ('+@pivot_cols+')
    ) as pivottable'
    EXEC(@pivot_query);

    Try the Above Code it'll dynamically creates the columns.
    • Marked as answer by moondaddy Friday, August 16, 2013 3:15 PM
    Friday, August 16, 2013 6:14 AM
  • Outstanding.  Thank you Guna, that worked perfect!
    Friday, August 16, 2013 3:15 PM