MS SQL Stuff: 2014

/****** Object: StoredProcedure [dbo].[PVIOT_OR_UNPIVOT _GIVENTABLE] Script Date: 04/10/2014 14:36:55 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Alter Procedure [dbo].[Pivot_Unpivot_Table]

(

@dbname varchar(512)

,@schemaname varchar(512)

,@srcTblName varchar(512)

,@trgtTblName varchar(512)

,@keyCols varchar(8000)

,@pivotCol varchar(512)

,@valueCol varchar(512)

,@debug bit=1

,@pivotorunpivot varchar(100)

)

/* Test:

---Pivot test

IF object_ID('test', 'U') IS NOT NULL

drop table test

create table test ( studebt_id int,student_name varchar(100), attr_name varchar(100), attr_value varchar(100))

insert into test

select 1,'bhasker', 'age', '24'

union all select 1,'bhasker', 'sex', 'F'

union all select 2,'suresh', 'age', '28'

union all select 2, 'suresh','sex', 'M'

union all select 3,'raju', 'age', '28'

union all select 3, 'raju','sex', null

union all select 4, 'prabhu','sex', 'F'

SELECT * from test

EXEC [dbo].[Pivot_Unpivot_Table]

@dbname ='test_database'

,@schemaname='dbo'

,@srcTblName ='test'

,@trgtTblName='test_pivot'

,@keyCols ='''studebt_id'',''student_name'''

,@pivotCol='attr_name'

,@valueCol='attr_value'

,@debug =0

,@pivotorunpivot='PIVOT'

select * from test_pivot

--UNPIVOT test------

IF object_ID('test', 'U') IS NOT NULL

drop table test

create table test (student_id INT, student_Name varchar(100), age int, sex varchar(100),height float ,salary money, DOB datetime)

insert into test

select 1,'bhasker', 24, 'M', 172.2, 1456.99, '1984-06-24'

union all select 2,'suresh', 29, 'M', 180,NULL, NULL

union all select 3, 'raju', 34, NULL, 176.9, 7899.9, null

union all select 4, 'prabhu', null, 'M', 189, NULL, '1987-09-17'

select * from test

EXEC [dbo].[Pivot_Unpivot_Table]

@dbname ='Test_database'

,@schemaname='dbo'

,@srcTblName ='test'

,@trgtTblName='test_unpivot'

,@keyCols = N'''student_id'', ''student_Name'''

,@pivotCol='attributeName'

,@valueCol='attributevalue'

,@debug =0

,@pivotorunpivot='UNPIVOT'

select * from test_unpivot

drop table test_unpivot

BEGIN

SET NOCOUNT ON

declare @database_name varchar(100),@server_name varchar(256), @start_Time smalldatetime,

@startLoadTime smalldatetime, @proc varchar(256), @date varchar(30), @loadSPid varchar(4),

@netAddr nchar(12),@clPrgName nvarchar(60), @spLoginTime varchar(30), @hostName nvarchar(20),

@spLoginName nvarchar(50),@nln varchar(6), @ntab varchar(200),@message varchar(1024),

@procName varchar (100),@SQL nvarchar (1024), @studentsTlb varchar(1024),@StudentsunpivotedTlb varchar (1024),

@colname varchar (100),@conv_cols nvarchar (4000),@cols NVARCHAR(4000)

declare @ErrMsg nvarchar(4000), @ErrSev int, @ErrSt int, @ErrProc nvarchar(126),

@ErrLn int, @ErrNr int, @ErrLoc nvarchar(300),@P_session_name varchar(256),

@Rowstxt int,@inclTxtColsNoCast varchar(512)

--DECLARE @column_List nvarchar(4000), @Paramdefination nvarchar(1024)

select @proc=DB_NAME()+'.'+ss.name+'.'+so.name from sys.objects so, sys.schemas ss

where so.object_id = @@PROCID and so.schema_id=ss.schema_id

select @database_name=DB_NAME(),@server_name = @@ServerName , @start_Time = getDate(),

@startLoadTime = getDate(), @date = convert(varchar(30),getDate(),109),

@nln = ''+char(10)+' ', @ntab=@proc+'> '

select @loadSPid=sp.spid, @hostName=substring(sp.hostname,0,len(sp.hostname)),

@spLoginName =substring(sp.loginame,0,len(sp.loginame)),

@spLoginTime=convert(varchar(20),sp.login_time,109),@netAddr=sp.net_address,

@clPrgName = program_name

from sys.sysprocesses sp where sp.spid= @@spid

BEGIN TRY

IF @debug=1

raiserror('Entering Procedure %s at %s%s', 0,1,@proc,@date,@nln) WITH NOWAIT

IF (@dbname IS NULL OR @schemaname IS NULL OR @srcTblName IS NULL OR @trgtTblName IS NULL

OR @keyCols IS nULL OR @pivotCol IS NULL OR @valueCol IS NULL )

BEGIN

raiserror('Input parameters cannot be null, Please check the input parameter values for below variables

(@dbname, @schemaname, @srcTblName, @trgtTblName, @keyCols, @pivotCol, @valueCol ',16,-1) WITH NOWAIT

END

SELECT @SQL='IF OBJECT_ID('''+@dbname+'.'+@schemaname+'.'+@trgtTblName+''', ''U'') is not null

drop table '+@dbname+'.'+@schemaname+'.'+@trgtTblName +''

IF @debug=1

BEGIN

raiserror('Drop target table if exists using below query %s%s', 0,1,@nln, @SQL) WITH NOWAIT

END

EXEC(@SQL)

IF (@pivotorunpivot='PIVOT') OR (@pivotorunpivot='UNPIVOT')

BEGIN

IF (@pivotorunpivot='PIVOT')

BEGIN

IF @debug=1

raiserror('Generating pivot data for given table %s',0,1, @nln ) with nowait

DECLARE @column_List nvarchar(4000), @Paramdefination nvarchar(1024)

SELECT @column_List=''

SELECT @SQL='SELECT @column_List=@column_List+ ''[''+'+@pivotCol+'+''],'' FROM

(SELECT DISTINCT '+@pivotCol+' from '+ @dbname+'.'+@schemaname+'.'+@srcTblName +') a'

SELECT @Paramdefination='@column_List varchar(1024) OUTPUT'

EXEC sp_executesql @SQL, @paramdefination , @column_List=@column_List output

SELECT @column_List=LEFT(@column_List, LEN(@column_List)-1)

SELECT @SQL='SELECT '+ replace(@keycols,'''','') + ', '+ @column_List+ '

INTO '+ @dbname+'.'+@schemaname+'.'+@trgtTblName +'

FROM

(SELECT '+ replace(@keycols,'''','') + ' , '+ @pivotCol+ ' , '+ @valueCol +'

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

PIVOT

(

MAX ('+@valueCol+')

FOR '+@pivotCol+' IN

( '+@column_List+' )

) AS pvt

ORDER BY pvt.'+ replace(@keycols,'''','') + ''

IF @debug=1

raiserror('Pivoting using below query %s %s',0,1, @nln, @SQL)WITH NOWAIT

EXEC(@SQL)

END

IF (@pivotorunpivot='UNPIVOT')

BEGIN

IF @debug=1

raiserror('Generating unpivot data for given table %s',0,1, @nln ) with nowait

SELECT @sql ='SELECT @conv_cols = Coalesce(@conv_cols,'''')+'', ''

+ ''ISNULL(convert (varchar(1024),''+QUOTENAME(name)+''),'''''''') as ''+QUOTENAME(name)

FROM sys.columns

WHERE [object_id] = OBJECT_ID('''+@srcTblName+''')

AND name NOT IN ('+@keycols+')';

SELECT @Paramdefination='@conv_cols nvarchar(4000) OUTPUT'

EXEC sp_executesql @sql, @Paramdefination, @conv_cols=@conv_cols OUTPUT

SELECT @sql ='SELECT @cols = Coalesce(@cols,'''') +'', '' + QUOTENAME(name)

FROM sys.columns

WHERE [object_id] = OBJECT_ID('''+@srcTblName+''')

AND name NOT IN ('+@keycols+')';

SELECT @Paramdefination='@cols nvarchar(4000) OUTPUT'

EXEC sp_executesql @sql, @Paramdefination, @cols=@cols OUTPUT

SELECT @sql = N'SELECT ' + replace(@keycols,'''','') + ', '+@pivotcol+ ', NULLIF('+@valuecol+','''') as '+ @valuecol+'

INTO '+ @dbname+'.'+@schemaname+'.'+@trgtTblName +'

FROM

(

SELECT ' + replace(@keycols,'''','') + @conv_cols + '

FROM '+ @dbname+'.'+@schemaname+'.'+@srcTblName +'

) AS cp

UNPIVOT

(

'+@valuecol+' FOR '+@pivotcol+' IN (' + Stuff(@cols, 1, 1, '') + ')

) AS up;';

IF @debug=1

raiserror('UnPivoting using below query %s %s',0,1, @nln, @SQL)WITH NOWAIT

EXEC sp_executesql @sql

END

END ELSE BEGIN

raiserror ('@pivotorunpivot paarameter values should be pivot or unpivot only(%s)', 16, -1, @pivotorunpivot) WITH NOWAIT

END

SET @date = cast(getdate() as varchar(30))

IF @debug=1

raiserror('%s Leaving procedure %s at %s %s',0,1,@nln,@procName, @date, @nln ) with nowait

END TRY

BEGIN CATCH

select @ErrMsg =ERROR_MESSAGE(), @ErrSev =ERROR_SEVERITY(), @ErrSt =ERROR_STATE(),

@ErrProc =ERROR_PROCEDURE(), @ErrLn = ERROR_LINE(), @ErrNr = ERROR_NUMBER()

raiserror('Original error nr %d, state %d, sev %d; line %d, proc %s %sError msg: %s',

16,-1, @ErrNr,@ErrSt,@ErrSev,@ErrLn,@ErrProc,@nln,@ErrMsg)

END CATCH

END

MS SQL Stuff

Monday, April 14, 2014

Dynamically Pivot OR Unpivot given table