I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Transpose data

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

Print | posted on Tuesday, March 15, 2005 10:36 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Transpose data

hi

Its Really good, but Cant we have complete tranpose of rows to cols, here we need to Keep one column as PrimaryCol..

Like
rowA
rowB
rowC
rowD

colA colB colC colD

In this format

Thanks
1/3/2008 1:17 PM | Balamurali
Gravatar

# re: Transpose data

Please show me how do i convert rows into columns with out using pivot.
9/14/2009 9:15 AM | Niveditha
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET