Transpose Query Output

,

Description

This script will return result of the query passed as parameter in transposed form. It works on all version of Sqlserver starting with 2k. 
Along with query definition you can limit number of rows specifying @Top parameter with max value of 1000. If you want original query result, you can pass value 1 to @Original parameter. 

Usage

EXEC [dbo].[transpose_query_output]
@Query =
'
select 1 id
, ''red'' Colour
, ''Primary'' type
union
select 2 
, ''yellow'' 
, ''Primary''
union
select 3 
, ''orange'' 
, ''Secondary''
'
,@Top=100
,@Orignal = 1

Known limitations

  • query definition can't exced 8k chars.
  • query must be "select" type
  • can't support identity columns in select list
CREATE PROCEDURE [dbo].[transpose_query_output]
(	@Query		varchar(8000)
,	@Top		int = 1000
,	@Orignal	bit = 0
)
AS
begin

declare	@cr	char(2)
,	@x	varchar(1000)
,	@cTop	varchar(100)

set @cr=char(13)+char(10)
set @x = case when @Orignal=1 then 'select * from #ttrspqop1' else '' end
set @Top=case when @Top>1000 then 1000 else @Top end
set @cTop = convert(varchar(100),@Top)

exec
('declare @n int'+@cr
+', @i int'+@cr
+', @m int'+@cr
+', @j int'+@cr
+', @_cn varchar(300)'+@cr
+', @_cr varchar(300)'+@cr
+', @_cv varchar(8000)'+@cr
+', @xSql nvarchar(4000)'+@cr
+'select top '+@cTop+' identity(int,1,1)	[#rec],* into #ttrspqop1 from (select * from ('+@Query+') x) y'+@cr
+'set @n=@@rowcount+1'+@cr
+'use tempdb'+@cr
+'select colid-1 [#colid],name [#colname], case when xusertype in (select xusertype from systypes where name like ''%date%'') then 1 else 0 end Is_Date into #ttrspqopX from syscolumns where colid<>1 and id in (select id from sysobjects where name like ''#ttrspqop1%'') order by colid'+@cr
+'select [#colid],[#colname] into #ttrspqop2 from #ttrspqopX '+@cr
+'set @m = @@rowcount+1'+@cr
+'set @i=1'+@cr
+'while @i<@n begin'+@cr
+	'set @_cn=''[#rec ''+convert(varchar(100),@i)+'']'''+@cr
+	'exec(''alter table #ttrspqop2 add ''+@_cn+'' varchar(8000)'')'+@cr
+	'set @j = 1'+@cr
+	'while @j<@m begin'+@cr
+		'select	@_cr=case when Is_Date=1 then ''convert(varchar(100),[''+[#colname]+''],121)'' else ''[''+[#colname]+'']'' end'+@cr
+		'from	#ttrspqopX'+@cr
+		'where	[#colid]=@j'+@cr
+		@cr
+		'set @xSql = N''select @v=convert(varchar(8000),''+@_cr+'') from  #ttrspqop1 where [#rec]=@nr'' '+@cr
+		'exec sp_executesql @xSql,N''@v varchar(8000) OUTPUT,@nr int'',@_cv OUTPUT,@i'+@cr
+		@cr
+		'set @xSql = N''update #ttrspqop2 set ''+@_cn+''=''''''+@_cv+'''''' where [#colid]=@nc'' '+@cr
+		'exec sp_executesql @xSql,N''@nc int'',@j'+@cr
+		'set @j = @j + 1'+@cr
+	'end'+@cr
+	'set @i = @i+1'+@cr
+'end'+@cr
+'select * from #ttrspqop2'+@cr
+@x+@cr
)
return(0)
end

Rate

4 (3)

Share

Share

Rate

4 (3)