Convert Column Header to Row In SQL Server

We all might have come accross scenarios when it is required to convert a column header of a table to a row. It may be because there is a requirement when we want to generate a report that shows data vertically instead of horizontally if number of columns contained in the table is more than number of rows, or any other reason. So we will see how we can do this quickly:

Let's create a table and we name it as "t"

create table t (ID int identity(1,1),Book varchar(20),PriceInJan int,PriceInFeb Int )

Populate the table as below


insert t values('Fiction',20,31)
insert t values('Science',15,17)
insert t values('History',15,18)
insert t values('Non-Fiction',12,17)
insert t values('Autobiography',18,16)
insert t values('Other',16,12)


select * from t

--Select from table "t" will give

 

Now our query for converting column header to Row data

DECLARE @sql varchar(max), @table varchar(50)

DECLARE @sql1 varchar(max)

SELECT @sql = ' ', @table = 't'

SELECT @sql = @sql + '

SELECT ID, Book

, (''' + column_name + ''')

AS RowTypes

,Convert(float,' + column_name + ') AS Price

FROM ' + table_name + '

UNION '

FROM information_schema.columns

WHERE table_name=@table AND column_name<>'ID'

and COLUMN_NAME <>'Book'

SELECT @sql = Left(@sql,Len(@sql)-5)

exec(@SQL)

One of the important thing to note is that we have filtered out where column_name is ID or Book, else it would return these columns header as row data as well, as shown below:

DECLARE @sql varchar(max), @table varchar(50)

DECLARE @sql1 varchar(max)

SELECT @sql = ' ', @table = 't'

SELECT @sql = @sql + '

SELECT ID, Book

, (''' + column_name + ''')

AS RowTypes

,Convert(varchar,' + column_name + ') AS Price

FROM ' + table_name + '

UNION '

FROM information_schema.columns

WHERE table_name=@table --AND column_name<>'ID'

--and COLUMN_NAME <>'Book'

SELECT @sql = Left(@sql,Len(@sql)-1)

exec(@SQL)

Output for above will be :

 

Tags: , , , ,

SQL Server | SQL Tricks