Converting rows to columns in SQL Server

Question

I have a view vw_Country which returns the following records:

I want to write a query using this view to return following results:

Any help would be appreciated!

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — e_i_pi, Mar 29 '18 at 02:20
Thanks for sharing link. But I don't have any column to perform aggregate on. — Mandar Patil, Mar 29 '18 at 02:51

score 1 · Accepted Answer · answered Mar 29 '18 at 03:17

Without a column to pivot on, it isn't possible, but you can create a column to pivot on. Something like this should probably work:

SELECT Rownum, [Australia], [India], [United States]
FROM 
    (SELECT ROW_NUMBER() OVER(PARTITION BY Country, ORDER BY City) AS Rownum, Country, City
        FROM vw_Country
        ) sourcetable
    PIVOT 
        (
        MIN(City)
        FOR Country IN ([Australia], [India], [United States])
        ) AS pvt
ORDER BY pvt.Rownum

score 0 · Answer 2 · answered Mar 29 '18 at 03:34

Create another column by using rank then use pivot.

Select [Australia], [India], [United States]
from 
(
  select Country, City, 
   Rank() over (partition by country order by city) as rnk
  from yourTable
) src
 pivot
(
 Max(City)
  for Country in ([Australia], [India], [United States])
) piv;

Converting rows to columns in SQL Server

2 Answers2