How can I get all items to display as columns using SQL pivot when number of items is large?

Question

If I have a table like so:

SalesPerson Product SalesAmount
Bob         Pickles $100.00
Sue         Oranges $50.00
Bob         Pickles $25.00
Bob         Oranges $300.00
Sue         Oranges $500.00

and I wanted to get

SalesPerson Oranges Pickles
Bob         $300.00 $125.00
Sue         $550.00

I would use:

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM 
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

However this would be a bit of a pain if I had a large number of products and I wanted to get the same result. I'm guessing I could use a Select statement like so:

FOR Product IN(SELECT DISTINCT(Product) FROM ProductSales

but I would still need to define the individual column headings i.e.

    SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles, [Bagels] AS Bagels, [MonsterTrucks] AS MonsterTrucks...

Is there a more efficient way of going about achieving this?

No doubts,you have to use dynamic sql to do so.If you are to display in front like asp.net then write simple query in proc and do the pivot part in c#, for example — KumarHarsh
no problem,even dynamic sql is easy and fast.you can just start over with 2-3 items. — KumarHarsh

KumarHarsh KumarHarsh · Accepted Answer · 2015-03-10T11:13:57

see,i do not know you exact table design. I am just trying to give you rough idea here,And this is just main part of whole query .

 Declare @item varchar(500)
    Declare @Sql varchar(max)

    create   table t (SalesPerson varchar(50), Product varchar(50), SalesAmount float)
    insert into t values
    ('Bob','Pickles', 100.00)
    ,('Sue','Oranges', 50.00)
    ,('Bob','Pickles', 25.00)
    ,('Bob','Oranges', 300.00)
    ,('Sue','Oranges', 500.00)

    ;With CTE as
    (
     Select distinct product from t
    )

    select 

        @item=stuff((select ','+ '['+ product + ']' from cte for xml path('')),1,1,'')
     from CTE A
    print @item --debug

    set @sql=' SELECT SalesPerson, '+@item+'
    FROM 
    (SELECT SalesPerson, Product, SalesAmount
    FROM t ) ps
    PIVOT
    (
    SUM (SalesAmount)
    FOR Product IN
    ( '+@item+')
    ) AS pvt '
    print @sql --debug
    exec (@sql)

How can I get all items to display as columns using SQL pivot when number of items is large?

1 Answers