CODEWITHSUNDEEP
sqlsql-server-2008
GayanSanjeewa
GayanSanjeewa

Reputation: 366

Convert Rows to Columns SQL 2008

I just want to transpose following table

RegionID  Region     RedionCode     RegionSupervisor 

1     Eastern    E01         Mark
2     Western    W01         Jim
3     Northern    N01        Paul
4     Southern    S01        David

to

Eastern         Western        Northern           Southern

1          2         3            4
E01        W01       N01          S01
Mark       Jim       Paul         David

I use SQL 2008. Any help would be really appreciated

cheers!

Upvotes: 3

Views: 2674

Answers (3)

engil
engil

Reputation: 359

You can use PIVOT, but you will also need to incorporate dynamic SQL, as PIVOT by itself will only support situations where you know in advance the full set of columns in the resultant table. Pivots with Dynamic Columns in SQL Server 2005

Upvotes: 1

GayanSanjeewa
GayanSanjeewa

Reputation: 366

Ok guys, finally I found a way of doing it but possibly not the more effective way. I could not find a solution with PIVOT yet

BEGIN
    DECLARE @ColumnList varchar(200)
    DECLARE @ColumnInList varchar(200)
    DECLARE @TableName varchar(20)
    DECLARE @TableScript varchar(2000)                         

    SET @ColumnList = ''
    SET @ColumnInList = ''
    SELECT @ColumnInList += RTRIM(RegionDescription) + ',', @ColumnList += '[' + RTRIM(RegionDescription) + '] varchar(50) , '
    FROM RegionSup 

    SET @ColumnList = LEFT(@ColumnList, LEN(@ColumnList) - 1)
    SET @ColumnInList = LEFT(@ColumnInList, LEN(@ColumnInList) - 1)

    SELECT @TableName = 'TEMP' + CONVERT(char(12),GETDATE(),14);
    SELECT @TableName = REPLACE(@TableName,':','')
    SET @TableScript = 'CREATE TABLE ' + @TableName + ' (' +
                    @ColumnList + ')'

    EXECUTE (@TableScript)


    --Column Values
    DECLARE @RegionID varchar(30)
    DECLARE @RegionSupervisor varchar(50)
    DECLARE @RegionCode varchar(50)
    --End Column Values

    SET @RegionID = ''
    SET @RegionSupervisor = ''
    SET @RegionCode = ''

    SELECT @RegionID += '''' + CONVERT(varchar(10),RegionID) + ''',',
           @RegionSupervisor += '''' + RegionSupervisor + ''',',
           @RegionCode +=  '''' + RegionCode + ''','
    FROM RegionSup

    SET @RegionID = LEFT(@RegionID,LEN(@RegionID) - 1)
    SET @RegionSupervisor = LEFT(@RegionSupervisor,LEN(@RegionSupervisor) - 1)
    SET @RegionCode = LEFT(@RegionCode,LEN(@RegionCode) - 1)

    DECLARE @InsertStatement nvarchar(max)
    SET @InsertStatement = ''

    SET @InsertStatement = 'INSERT INTO ' + @TableName + '(' +  @ColumnInList + ') VALUES ' +
                            '(' + @RegionID + '),' +
                            '(' + @RegionSupervisor + '),' +
                            '(' + @RegionCode + ')' 

    EXECUTE(@InsertStatement)                       

    EXECUTE('SELECT * FROM ' + @TableName)
    EXECUTE('DROP TABLE ' + @TableName)
END

Upvotes: 0

womp
womp

Reputation: 116977

You can do this using the PIVOT and UNPIVOT tsql commands.

Upvotes: 4

Related Questions