Make a Dynamic Pivot Table in SQL Server

This blog could solve your problem as I was faced this type of problem. So I share this code with you.

I have one table. This table has total eight records. I want to show these records as column by.

My table data and assume this table name is cityreocrds.

Sr.No.                    City                    Pincode

   1                          Delhi                  110096
   2                          Kaithal               136027
   3                          Karnal                136028
   4                          Kurukshetra      130029

I want this data as by column

Sr.No.                     Delhi                       Kaithal                      Karnal                       Kurukshetra
   1                           110096                   136027                      130028                      130029

Note: Without pivot table it's a difficult task.

declare @columns varchar(max)=null
declare @temporary varchar(max)=null

The above check we have two variable declare one is @column and another is @temporary.

SELECT @columns = COALESCE(@columns + ','+QUOTENAME(city),QUOTENAME(city))FROM CityRecords

The @columns hold the name of city with comma separated. As like this:

[delhi],[Kaithal],[Karnal],[Kurukshetra]

set @temporary='select *
from
(
   select c.city as orgname,case when c.pincode=null then '''' else ''yes'' end as orgcode
   from citycode as c
) d
pivot
(
   max(orgcode) for orgname in ('+@columns+')
) as t2'
exec (@temporary)

I hope it's useful every buddy. Please comment feel free if any confusion create in mind or related code.