Dynamically Unpivot Given Table.

UNPIVOT performs by rotating columns into rows.Using below script you can unpivot any given table
by mentioning key columns.

IF OBJECT_ID('[dbo].[unpivot_dynamically]','P') IS NOT NULL

DROP PROCEDURE [dbo].[unpivot_dynamically]

GO

CREATE procedure [dbo].[unpivot_dynamically]

@schemaname varchar(512)

,@srcTblName varchar(512)

,@keycol varchar(512)

,@pivotCol varchar(512)=null

,@valueCol varchar(512)=null

,@debug bit=0

as

begin

set nocount on

if ( @schemaname is null or @srcTblName is null )

---- it will check the any input parameter is NULL

begin

print'input parameters are null, please provide proper values'

return(1)

end

DECLARE @CASTCOLUMNS varchar(1024),@COLUMNLIST VARCHAR(1024),@keycoumns varchar(1024),@SQLSTRING NVARCHAR(1024),@PARAMDEFINATION NVARCHAR(1024)

SELECT @keycoumns=''''+REPLACE(@keycol,',',''',''')+''''

SELECT @SQLSTRING='SELECT @COLUMNLIST=coalesce(@COLUMNLIST,'''')+''[''+COLUMN_NAME+'']''+'','' from information_schema.columns

where table_name ='''+@srcTblName+'''

and TABLE_SCHEMA='''+@schemaname+'''

and COLUMN_NAME not in ('+@keycoumns+')

'

SELECT @PARAMDEFINATION='@srcTblName nvarchar(512),@schemaname nvarchar(512),@keycoumns nvarchar(512),@COLUMNLIST NVARCHAR(1024) OUTPUT'

EXECUTE sp_executesql @SQLString,@PARAMDEFINATION,

@srcTblName = @srcTblName

,@schemaname=@schemaname

,@keycoumns=@keycoumns

,@COLUMNLIST =@COLUMNLIST OUTPUT;

SELECT @COLUMNLIST=SUBSTRING(@COLUMNLIST,1,LEN(@COLUMNLIST)-1)

SELECT @SQLSTRING='select @CASTCOLUMNS=coalesce(@CASTCOLUMNS,'''')+''coalesce (cast (['' + COLUMN_NAME + ''] as varchar (max)), ''''9876543212345678'''') as ''+ ''[''+COLUMN_NAME+'']''+'','' from information_schema.columns

where table_name ='''+@srcTblName+'''

and TABLE_SCHEMA='''+@schemaname+'''

and COLUMN_NAME not in ('+@keycoumns+')

'

SELECT @PARAMDEFINATION='@srcTblName nvarchar(512),@schemaname nvarchar(512),@keycoumns nvarchar(512),@CASTCOLUMNS NVARCHAR(1024) OUTPUT'

EXECUTE sp_executesql @SQLString,@PARAMDEFINATION,

@srcTblName = @srcTblName

,@schemaname=@schemaname

,@keycoumns=@keycoumns

,@CASTCOLUMNS =@CASTCOLUMNS OUTPUT;

SELECT @CASTCOLUMNS=SUBSTRING(@CASTCOLUMNS,1,LEN(@CASTCOLUMNS)-1)

select @SQLString = 'SELECT ' + @keycol + ',' + @pivotCol + ', nullif(' + @valueCol + ',''9876543212345678'' ) as '+@valueCol+'

FROM (SELECT ' + @keycol + ', ' +@CASTCOLUMNS +'

FROM '+@schemaname+'.'+@srcTblName+ ' ) p

UNPIVOT

(' + @valueCol+ ' FOR ' + @pivotCol + ' IN

( '+ @COLUMNLIST + ' )

)AS unpvt'

IF @debug=1

print @SQLString

EXEC(@SQLString)

end

GO
EX:::

Create table Unpivot_test

(

id int,

name varchar(100),

age int,

city varchar(100),

dob datetime,

salary money,

comment text

)

go

insert into Unpivot_test

select 1,'ABC',12,'waltham','12/2/2000',4000,'testing1'

union all

select 2,'DEF',13,'natic','12/5/2000',3000,'testing2'

union all

select 3,'GHY',12,'boston',null,null,null

SELECT * FROM Unpivot_test

Using Above Sp, unpivoting above table

EXEC [dbo].[unpivot_dynamically]

@schemaName = 'dbo',@srcTblName = 'Unpivot_test',

@keyCol = 'id,name',

@pivotCol = 'property_name',

@valueCol = 'Property_value',

@debug=0