pivot.sql

pivot.sql

create function dbo.fnSqlPivot(@sqlCheck nvarchar(4000), @sqlVal nvarchar(4000), @type nvarchar(128)) returns nvarchar(4000) as begin
	declare @s nvarchar(4000)
	declare @a nvarchar(4000)
	declare @b nvarchar(4000)
	declare @c nvarchar(4000)
	select 
		@c='))end', 
		@b='case when '+@sqlCheck+' then '+@sqlVal+' end',
		@a='case when max(case when('+@sqlCheck+')and(('+@sqlVal+') is not null)then 1 else 0 end)=1 then max(isnull(',
		@s = case lower(@type)
			when 'image' then @a+'convert(varbinary(max),' +@b+'),0x'+@c
			when 'text' then @a+'convert(varchar(max),' +@b+'),'''''+@c
			when 'ntext' then @a+'convert(varchar(max),' +@b+'),'''''+@c
			when 'date' then 'convert([date],' +@a+'convert(varchar(32),' +@b+',121),'''''+@c+',121)'
			when 'time' then 'convert([time],' +@a+'convert(varchar(32),' +@b+',127),'''''+@c+',127)'
			when 'smalldatetime' then 'convert([smalldatetime],' +@a+'convert(varchar(64),' +@b+',127),'''''+@c+',127)'
			when 'datetime' then 'convert([datetime],' +@a+'convert(varchar(64),' +@b+',127),'''''+@c+',127)'
			when 'datetime2' then 'convert(datetime2,' +@a+'convert(varchar(128),' +@b+',127),'''''+@c+',127)'
			when 'tinyint' then @a+@b+',0'+@c
			when 'bit' then @a+@b+',0'+@c
			when 'smallint' then @a+@b+',-32768'+@c
			when 'int' then @a+@b+',-2147483648'+@c
			when 'bigint' then @a+@b+',-9223372036854775808'+@c
			when 'float' then @a+@b+',-1.79E+308'+@c
			when 'real' then @a+@b+',-3.40E+38'+@c
			when 'money' then @a+@b+',$-922337203685477.5808'+@c
			when 'smallmoney' then @a+@b+',$-214748.3648'+@c
			when 'varbinary' then @a+@b+',0x'+@c
			when 'varchar' then @a+@b+','''''+@c
			when 'binary' then @a+@b+',0x'+@c
			when 'char' then @a+@b+',char(0)'+@c
			when 'nvarchar' then @a+@b+','''''+@c
			when 'nchar' then @a+@b+',char(0)'+@c
			when 'xml' then 'convert(xml,' +@a+'convert(nvarchar(max),' +@b+'),'''''+@c+')'
			else 'convert(' +@type+',' +@a+'convert(nvarchar(128),' +@b+'),'''''+@c+')'
		end
	return @s
end
go