CODEWITHSUNDEEP
sql-server
Venkat
Venkat

Reputation: 15

SQL dynamic pivot for multiple columns

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Symbol) 
                from Opt 
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')   
set @query = 'SELECT Date,' + @cols + ' from 
         (
            select Date, Symbol, Price
            from Opt 
        )x
        pivot 
        (
            max(Price)
            for Symbol in (' + @cols + ')
        ) p'

execute(@query);

I get this from the above code:

Symbols(varchar50)       Date     price      quantity
apple              14/11/2016    30       15
banana             14/11/2016    22       20

i need like this

Date        apple_price  apple_quantity    banana_price  banana_quantity
14/11/2016  30           15                  22          10

from above code i get only price

Upvotes: 1

Views: 2083

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Just a minor twist to your original. Notice the sub-query/Union All.

Declare @SQL varchar(max)
Select  @SQL = Stuff((Select Distinct 
                             ',' + QuoteName(Symbol+'_Price') 
                           + ',' + QuoteName(Symbol+'_Quantity') 
                      From   Opt For XML Path('')),1,1,'')   
Select  @SQL = 'Select Date,' + @SQL + ' 
                From (
                      Select Date,Item=Symbol+''_Price'',Val=Price From Opt
                      Union All
                      Select Date,Item=Symbol+''_Quantity'',Val=Quantity From Opt
                     ) A
                Pivot (max(Val) For Item in (' + @SQL + ') ) p'

Exec(@SQL);

Returns

Date         apple_Price    apple_Quantity  banana_Price    banana_Quantity
2016-11-14   30             15              22              20

Upvotes: 2

Related Questions