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 &开发者_JAVA百科nbsp;       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!

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

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

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