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.