Pivot in SQL Server

Pivot in SQL Server

 

CREATE table RAGHUDB.DBO.TEST_PIVOT(id int,name varchar(20),city varchar(20),value int)

INSERT into RAGHUDB.DBO.TEST_PIVOT values(1,'A','CHE',100)

INSERT into RAGHUDB.DBO.TEST_PIVOT values(1,'A','BLR',200)

INSERT into RAGHUDB.DBO.TEST_PIVOT values(2,'B','CHE',300)

INSERT into RAGHUDB.DBO.TEST_PIVOT values(2,'B','BLR',400)

INSERT into RAGHUDB.DBO.TEST_PIVOT values(2,'B','DEL',500)

INSERT into RAGHUDB.DBO.TEST_PIVOT values(3,'C','HYD',100)

Table Data

Select * from RAGHUDB.DBO.TEST_PIVOT

ID	NAME	CITY	VALUE
1	A	CHE	100
1	A	BLR	200
2	B	CHE	300
2	B	BLR	400
2	B	DEL	500
3	C	HYD	100

Query to pivot the data

Select ID,NAME,CHE,BLR,DEL,HYD from

(

SELECT id,name,city,value FROM [dbo].[TEST_PIVOT]) T1

PIVOT

(

MAX(value)

      FOR T1.City IN (CHE,BLR,DEL,HYD)) AS T2

         order by T2.name

Output of the query

ID	NAME	CHE	BLR	DEL	HYD
1	A	100	200	NULL	NULL
2	B	300	400	500	NULL
3	C	NULL	NULL	NULL	100