1
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

Matt
  • 13,833
  • 2
  • 16
  • 28
Venkat
  • 15
  • 7
  • 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 Nov 21 '16 at 18:52

1 Answers1

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66