T-SQL: Rotate Columns into Rows, Take 1
[edit: improved version here.]
The following procedure I wrote will rotate one row of SQL results into a column of name/value pairs.
Note: you can either specify a wildcard for all columns (*) or else pass in a piped list of columns
Related: PIVOT.
The following procedure I wrote will rotate one row of SQL results into a column of name/value pairs.
Note: you can either specify a wildcard for all columns (*) or else pass in a piped list of columns
Related: PIVOT.
/*
Usage: (ie AdventureWorks)
rotate 'Table_Schema', 'Table_Name', '|Columns|To|Rotate|', 'Where Clause'
Example:
rotate 'Person', 'Address', '*', 'addressid=1'
rotate 'Person', 'Address', '|AddressId|AddressLine1|PostalCode|', 'addressid=50'
compare with:
select AddressId, AddressLine1, PostalCode from Person.Address where addressid=1
Author:
Jeffrey.Knight@gmail.com Mon Sep 15 19:59:10 EDT 2008
*/
create procedure rotate(@table_schema varchar(25),
@table_name varchar(25),
@columns varchar(500),
@whereClause varchar(255)) as
set nocount on
if(@table_schema = '' OR @table_name = '' OR @columns = '' OR @whereClause = '') return
declare @colCount int
declare @columnName varchar(40)
declare @sql VARCHAR(max) ; set @sql = ''
declare @i int; set @i = 0
declare tableCursor cursor for select column_Name from information_schema.columns
where table_schema=@table_schema and table_name= @table_name
order by table_schema, table_name
set @colCount=@@ROWCOUNT
open tableCursor
fetch next from tableCursor into @columnName
while @@FETCH_STATUS = 0 begin
if(charindex('|' + @columnName + '|', @columns) > 0) OR charindex('*', @columns) > 0 begin
set @sql = @sql + ' select ''' + @columnName + ''' as Name, + cast(' + @columnName +
' as varchar(255)) as Value from ' + @table_schema + '.' + @table_name +
' where ' + @whereClause + '
UNION'
end
fetch next from tableCursor into @columnName
set @i = @i + 1
end
deallocate tableCursor
set @sql = substring(@sql, 0, len(@sql) - len('UNION'))
exec(@sql)