How to get following output using SQL server 2012

Question

My Table Schema is ID,Year,City,Population

And Data's are like below

ID  |Year    |City      |Population 
--- |------- |------    |----------
1   | 2016   | Chennai  | 1200000
2   | 2016   | Salem    | 120000
3   | 2015   | Chennai  | 1100000
4   | 2015   | Salem    | 200000

And I desire output like below

ID  |Year    |Chennai   |Salem    
--- |------- |------    | ----
1   | 2016   | 1200000  | 120000
2   | 2015   | 1100000  | 200000

Is it possible to get output like above

`pivot` is the word you need to include in your searches. Asked and answered *many* times. — Damien_The_Unbeliever, Aug 09 '16 at 13:30
I tried many times. But my efforts went vain. Because unaware of pivot. Could tell me how to use pivot — Daniel A Sathish Kumar, Aug 09 '16 at 13:35

score -1 · Answer 1 · answered Aug 09 '16 at 15:02

If you just need a query for these two towns, you can use this:

SELECT *
FROM 
(
  SELECT ROW_NUMBER() OVER (partition by City Order by City) As [ID], [Year], [City], [Population]
  FROM YourTable
) src
PIVOT
(
  SUM([Population])
  for [City] in (Chennai, Salem)
) piv
ORDER BY ID

If you need it to be dynamic you can transform it into a Table-Valued function and replace the towns with a string parameter.

If you need it to be dynamic and always show all cities then you can do it like this:

DECLARE @City nvarchar(255), @Cities nvarchar(max), @SQL nvarchar(max)

SET @Cities = ''

DECLARE cursor1 CURSOR FOR
SELECT DISTINCT City
FROM YourTable
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @City

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @Cities += @City + ', '

    FETCH NEXT FROM cursor1 INTO @City
END
CLOSE cursor1
DEALLOCATE cursor1

SET @Cities = SUBSTRING(@Cities, 0, LEN(@Cities))

SET @SQL = 'SELECT *
FROM 
(
  SELECT ROW_NUMBER() OVER (partition by City Order by City) As [ID], [Year], [City], [Population]
  FROM YourTable
) src
PIVOT
(
  SUM([Population])
  for [City] in ('+@Cities+')
) piv
ORDER BY ID'

EXEC(@SQL)

(Haven't used Pivot before so I figured this would be interesting to solve :))

How to get following output using SQL server 2012

1 Answers1