0

we are using sql server 2008 and data in below format.

enter image description here

and i want to above record in below pivot format. enter image description here

please help.

J R B
  • 2,069
  • 4
  • 17
  • 32
  • Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – SynozeN Technologies May 17 '17 at 06:38

1 Answers1

1
select * from  PivotEx
pivot
(
  avg(avg)
  for city in ( [Mumbai] ,[Ahmedabad],[Raikot])
) piv;

To pass the values dynamically in pivot

Declare @cols nvarchar(max)
Declare @query nvarchar(max)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(city) 
            FROM PivotEx
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = '  select * from PivotEx
                 pivot 
                 (
                     avg(avg)
                    for city in (' + @cols + ')
                 ) p '

execute(@query)
Samanvitha - user2519049
  • 374
  • 1
  • 7