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