none
Pivot and spreading elements

    Question

  • Hi all -

    I'm studying PIVOT and am baffled by one aspect.  All the examples that I've seen online and in the books - the spreading columns are specific values instead of retrieved as a list of unique values contained in a column.  The IN phrase is 'shipperid IN ([1], [2], [3])' or orderyear IN ([2007], [2008]. [2009]).  It seems like it should be IN UNIQUE(shipperid) as the list or YEAR(orderyear).

    This just seems unreuseable and just not very database-y (yea I made that up).   Why can't the spreading values be calculated at execute time? 

    As always - thanks much.


    Saturday, September 14, 2013 10:08 PM

Answers

  • That's done because the query optimizer that builds the plan for query (you can think of this as the "compile" phase) wants to know the number of columns, names and datatypes of those columns at compile time.  It wants to be able to build a plan that will work no matter what data is in the rows.  What you want to do is have a query where the number and names of the columns depend on the data in the rows.  And that violates the above rule.

    The solution is to use dynamic sql and do a dynamic pivot.  (Google dynamic pivot for lots of examples).  You do that by building a sql command and then executing it.  For example

    Create table #Test(SalesID int,ProductID int,PartyFirstName varchar(50),Sales int);
    Insert #Test(SalesID, ProductID, PartyFirstName, Sales) Values
    (1, 1, 'George', 25),
    (2, 1, 'Martha', 17),
    (3, 1, 'John', 3),
    (4, 2, 'Thomas', 4),
    (5, 2, 'Aaron', 15),
    (6, 2, 'Martha', 8);
    
    go
    DECLARE @cols AS NVARCHAR(MAX)
    declare @query  AS NVARCHAR(MAX)
    
    select @cols = STUFF((SELECT ',' 
                            + QUOTENAME(PartyFirstName)  
                        from #Test
    					Group By PartyFirstName
    					Order By PartyFirstName
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    -- select @cols
    set @query = 'SELECT ProductID, ' + @cols + ' from 
                 (
                    select ProductID,PartyFirstName, Sales
                    from #Test
                ) x
                pivot 
                (
                   sum(Sales)
                   for PartyFirstName in (' + @cols + ')
                ) p '
    -- select @query
    execute(@query)
    
    go
    drop table #Test

    Building a command and then executing it with sp_executesql or the execute command is called dynamic sql.  Dynamic sql is a great tool that allows you to do many neat things.  But if this is the first time you have seen it, be sure to read

    http://www.sommarskog.se/dynamic_sql.html

    first, especially the comments about sql injection.  If you are not careful, it is easy to open security holes in your system if you use dynamic sql.

    Tom

    Sunday, September 15, 2013 2:41 AM

All replies

  • That's done because the query optimizer that builds the plan for query (you can think of this as the "compile" phase) wants to know the number of columns, names and datatypes of those columns at compile time.  It wants to be able to build a plan that will work no matter what data is in the rows.  What you want to do is have a query where the number and names of the columns depend on the data in the rows.  And that violates the above rule.

    The solution is to use dynamic sql and do a dynamic pivot.  (Google dynamic pivot for lots of examples).  You do that by building a sql command and then executing it.  For example

    Create table #Test(SalesID int,ProductID int,PartyFirstName varchar(50),Sales int);
    Insert #Test(SalesID, ProductID, PartyFirstName, Sales) Values
    (1, 1, 'George', 25),
    (2, 1, 'Martha', 17),
    (3, 1, 'John', 3),
    (4, 2, 'Thomas', 4),
    (5, 2, 'Aaron', 15),
    (6, 2, 'Martha', 8);
    
    go
    DECLARE @cols AS NVARCHAR(MAX)
    declare @query  AS NVARCHAR(MAX)
    
    select @cols = STUFF((SELECT ',' 
                            + QUOTENAME(PartyFirstName)  
                        from #Test
    					Group By PartyFirstName
    					Order By PartyFirstName
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    -- select @cols
    set @query = 'SELECT ProductID, ' + @cols + ' from 
                 (
                    select ProductID,PartyFirstName, Sales
                    from #Test
                ) x
                pivot 
                (
                   sum(Sales)
                   for PartyFirstName in (' + @cols + ')
                ) p '
    -- select @query
    execute(@query)
    
    go
    drop table #Test

    Building a command and then executing it with sp_executesql or the execute command is called dynamic sql.  Dynamic sql is a great tool that allows you to do many neat things.  But if this is the first time you have seen it, be sure to read

    http://www.sommarskog.se/dynamic_sql.html

    first, especially the comments about sql injection.  If you are not careful, it is easy to open security holes in your system if you use dynamic sql.

    Tom

    Sunday, September 15, 2013 2:41 AM
  • Thank you for all the info.  This helps a lot....

    Beth


    Beth

    Sunday, September 15, 2013 9:49 PM