Make a Dynamic Pivot Table in SQL Server

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.

  1. declare @columns varchar(max)=null    
  2. declare @temporary varchar(max)=null   
The above check we have two variable declare one is @column and another is @temporary.
  1. 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]
  1. set @temporary='select *    
  2. from    
  3. (    
  4.    select c.city as orgname,case when c.pincode=null then '''' else ''yes'' end as orgcode    
  5.    from citycode as c    
  6. ) d    
  7. pivot    
  8. (    
  9.    max(orgcode) for orgname in ('+@columns+')    
  10. as t2'    
  11. exec (@temporary)   
I hope it's useful every buddy. Please comment feel free if any confusion create in mind or related code.
Ebook Download
View all
Learn
View all