SQL dynamic pivot for multiple columns

Question

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

Your code scares me. Use parameterzation to avoid SQL injection. Also, SQL has sys tables that can make this dynamic and portable (sys.tables and sys.columns come to mind). — clifton_h
– clifton_h, Commented Nov 21, 2016 at 18:52

John Cappelletti · Accepted Answer · 2016-11-21 19:37:39Z

2

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

edited Nov 21, 2016 at 19:37

answered Nov 21, 2016 at 19:19

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Venkat Over a year ago

It is working but taking much time can you modify it

John Cappelletti Over a year ago

@Venkat Happy to help

John Cappelletti Over a year ago

@Venkat When you say much time. How much time and how many records in your Opt table?

Collectives™ on Stack Overflow

SQL dynamic pivot for multiple columns

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related