SQL Pivot Table from dynamic columns

Question

I wrote this query to generate pivot table :

SELECT *
FROM (
    SELECT 
        ItemName_AR as [ItemName], 
        NetValue as Amount 
    FROM view_WH_CompanyTransactionDtl
) as s
PIVOT
(
    SUM(Amount)
    FOR [ItemName] IN (select ItemName_AR from WH_Items)
)AS pvt

but it cuases the following error in

Incorrect syntax near the keyword 'select'.

at this statment

FOR [ItemName] IN (select ItemName_AR from WH_Items)

score 2 · Answer 1 · answered May 21 '18 at 11:21

2

Unfortunately, you may not use a select statement to provide the variety of values (unpivoted form)=columns(pivoted form). You may either use IN ([value1],[value2]) etc or consider a dynamic solution, here's a link: SQL Server Dynamic Pivot Column Names

answered May 21 '18 at 11:21

George Menoutis

6,894
3
19
43

score 0 · Answer 2 · answered May 21 '18 at 11:36

You cannot create Dynamic SQL PIVOT like that. You may try something like this...

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename(ItemName_AR)
         FROM   WH_Items       
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =  'SELECT *
                                    FROM (
                                        SELECT 
                                            ItemName_AR as [ItemName], 
                                            NetValue as Amount 
                                        FROM view_WH_CompanyTransactionDtl
                                    ) as s
                                    PIVOT
                                    (
                                        SUM(Amount)
                                        FOR [ItemName] IN ('+ @cols +')
                                    )AS pvt';

EXECUTE(@query)

SQL Pivot Table from dynamic columns

2 Answers2