There were some quetions in the
forums about transposing data without the use of aggregate functions. While building a CSV list is nice sometime that's not what we want. So this is a sproc that does just that. It's nothing complex, it just gets the max number data in groups and builds an SQL statement with that many left joins.
create proc spTranspose
@TableName varchar(100),
@PrimaryCol varchar(100), -- parent column to transposing columns
@SecondaryCol varchar(1000) -- CSV string of columns to transpose
as
select @SecondaryCol = replace(@SecondaryCol, ' ', '')
declare @i int, @tInc varchar(10), @tIncNext varchar(10), @Sql varchar(8000),
@SqlSelect varchar(8000), @tempSql nvarchar(4000), @ColNum int
select @i=1, @Sql = '', @SqlSelect = ''
select @tempSql = 'select top 1 @ColNum = count(*) from ' + @TableName + ' group by ' + @PrimaryCol + ' order by count(*) desc'
exec sp_executesql @tempSql, N'@ColNum int output', @ColNum output
if object_id('tempdb..##temp_transpose') is not null
drop table ##temp_transpose
exec ('select identity (int, 1,1) as Transpose_Id, ' + @PrimaryCol + ', ' + @SecondaryCol +
' into ##temp_transpose from ' + @TableName + ' order by ' + @PrimaryCol + ', ' + @SecondaryCol)
select @SqlSelect = 'select t1.' + @PrimaryCol,
@Sql = ' from ##temp_transpose t1'
while @i <= @ColNum
begin
set @tInc = 't' + cast(@i as varchar(10))
set @SqlSelect = @SqlSelect + ', ' + @tInc + '.'+ replace(@SecondaryCol, ',', ', ' + @tInc + '.')
set @tIncNext = 't' + cast(@i+1 as varchar(10))
if @i<@ColNum
set @Sql = @Sql + ' LEFT JOIN ##temp_transpose ' + @tIncNext + ' on t1.' + @PrimaryCol + ' = ' +
@tIncNext + '.' + @PrimaryCol + ' and ' + @tInc + '.Transpose_Id = ' + @tIncNext + '.Transpose_Id-1 '
set @i = @i + 1
end
select (@SqlSelect + @Sql + ' WHERE (select count(*) from ##temp_transpose where t1.' + @PrimaryCol + ' = ' +
@PrimaryCol + ' and t1.Transpose_Id > Transpose_Id)=0 order by t1.' + @PrimaryCol)
go
exec spTranspose 'pubs..authors', 'au_lname', 'au_fname, phone'
go
drop procedure spTranspose