1. Create Example Table
-----code:start----------------------------------------------------
Create table #Sample_Table_1
(
num int
,descr varchar(20)
,[amount] decimal(9,2)
,[Date_1] smalldatetime
)
-----code:end----------------------------------------------------
2. Try to insert dummy data
-----code:start----------------------------------------------------
insert into #Sample_Table_1 values(101,'Blue Skirt', '20.00', '01/01/2008')
insert into #Sample_Table_1 values(102,'white socks', '5.000','12/03/2009')
insert into #Sample_Table_1 values(110,'blue shirt', '10.00','01/12/2010')
insert into #Sample_Table_1 values(200,'Red Tie', '15.99','05/03/2010')
insert into #Sample_Table_1 values(350,'Black Belt ', '100','02/09/2011')
-----code:end----------------------------------------------------
3. Check data in Example Table
-----code:start----------------------------------------------------
select * from #Sample_Table_1
-----code:end----------------------------------------------------
Result Messages:
4. For Check: Declare Variable for the first Column Header and set date as the header Column
-----code:start----------------------------------------------------
Declare @column_Header_1 varchar(8000)
Select @column_Header_1=
COALESCE(@column_Header_1+''',''','')
+cast(num as varchar(10)) +'''as'''
+convert(varchar(10),[date_1],3)
from #Sample_Table_1
print @column_Header_1
-----code:end----------------------------------------------------
Result Messages:
5. For Check: Declare second Row Header data
-----code:start----------------------------------------------------
declare @column_Header_2 varchar(8000)
select @column_Header_2=
COALESCE(@column_Header_2+''',''','')
+descr
from #Sample_Table_1
print @column_Header_2
-----code:end----------------------------------------------------
Result Messages:
6. For Check: Declare Third Row Header data
-----code:start----------------------------------------------------
declare @column_Header_3 varchar(8000)
select @column_Header_3=
COALESCE(@column_Header_3+''',''','')
+ cast([amount] as varchar(10))
from #Sample_Table_1
print @column_Header_3
-----code:end----------------------------------------------------
Result Messages:
7. Execute all variables as well as inserting our Coulumn header ( Run the query below )
-----code:start----------------------------------------------------
---@column_Header_1
-----code:start----------------------------------------------------
Declare @column_Header_1 varchar(8000)
Select @column_Header_1=
COALESCE(@column_Header_1+''',''','')
+cast(num as varchar(10)) +'''as'''
+convert(varchar(10),[date_1],3)
from #Sample_Table_1
-----code:end----------------------------------------------------
---@column_Header_2
-----code:start----------------------------------------------------
declare @column_Header_2 varchar(8000)
select @column_Header_2=
COALESCE(@column_Header_2+''',''','')
+descr
from #Sample_Table_1
-----code:end----------------------------------------------------
---@column_Header_3
-----code:start----------------------------------------------------
declare @column_Header_3 varchar(8000)
select @column_Header_3=
COALESCE(@column_Header_3+''',''','')
+ cast([amount] as varchar(10))
from #Sample_Table_1
-----code:end----------------------------------------------------
Exec (
'Select ''ID'' as ''Name'','''
+ @column_Header_1 + ''' union all select ''Item'','''
+ @column_Header_2 + ''' union all select ''Price'','''
+ @column_Header_3 + ''''
)
-----code:end----------------------------------------------------
Result Messages:
Source:
http://www.sqlservercentral.com/scripts/Coalese/72471/
Article: Column Headers To Row Headers