SQL Pivot

Pivot function can be used to convert rows in to columns in a data set. Following is an example that you may want to use this functionality. You have two tables.

tblContact

ContactID	ContactMethod	ContacText	PersonID
1	Email	wijitha@gmail.com	1
2	MobilePhone	07724579564	2
3	HomePhone	01754546566	3
4	Email	user@gmail.cpm	4
5	MobilePhone	01245655454	5
6	Email	user2@gmail.com	6

tblPerson

PersonID	FirstName	LastName
1	wijitha	wijenayake
2	Sandun	perera
3	chanaka	silva

You want to show each person with their contact methods in a Report or a Grid view. The normal SQL query that you write may looks like this.

select * from tblperson

inner join tblContact on tblperson.PersonID = tblContact.PersonID

Then the resultant data set will be like this.

Person ID	Contact ID	ContactMethod		ContactText
1	1		Email	wijitha@gmail.com
1	2		MobilePhone	07724579564
1	3		HomePhone	01754546566
2	4		Email	user@gmail.cpm
2	5		MobilePhone	01245655454
3	6		Email	user2@gmail.com

But now, it is very difficult to display each person’s records in a same row. It will be easy if data set returns “ContactMethod” types as columns. Here you can get use of PIVOT statement. Now your new query will be like this.

select tblperson.*, tblContact.ContactMethod, tblContact.ContactText into #temp from tblperson

inner join tblContact tblperson.PersonID = tblContact.PersonID

select * from #temp

pivot

(

max(ContactText)

FOR ContactMethod in ([Email], [MobilePhone], [HomePhone])

) AS P

drop table #temp

Resultant data set will be like this

PersonID	Emai	MobilePhone	HomePhone
1	wijitha@gmail.com	07724579564	01754546566
2	user@gmail.cpm	01245655454	NULL
3	user2@gmail.com	NULL	NULL

Now it is easy to display this data on a Report or a Grid view.

Syntax:

• SELECT columns
• FROM table
• PIVOT
  (
  Aggregate Function(Measure Column)
  FOR Pivot_Column IN ([Pivot Column Values])
  ) AS Alias

Note: Values (Email, HomePhone …) of pivot column (ContactMethod) should be a single word values.