"Rotate" Tables in SQL Server 2000

Posted on 2004-11-18
Last Modified: 2010-08-05
I've got a bit of a tricky question.  I've got one table, and I need to "rotate" the table.  Let's say that there are two columns in the table; the first column "Data Type," has values of Name, Rank, Serial.  The second column, "Data Values," has values of Tom Smith, Sargeant, 111.  What I need to do is "rotate" the table so that the values in the first column, Name, Rank, and Serial, become column _names_, and the values in the second column become corresponding cell _values_ to their previous rowmates.  

So, it needs to go from:
Name   |  Tom Smith
Rank    |  Sergeant
Serial   |  111

- to -

Name     -     Rank     -   Serial
Tom Smith  Sergeant       111

Some caveats:
We don't ever know what the values are in the first column of the existing table (the data is dynamic, so we don't know that it is "Name, Rank, Serial" ahead of time), so things like case statements won't work.
We'd prefer to not have this be an extended sproc, but if there isn't another way to do it, that's ok.

I'm assigning this a value of 250, because I'm not sure of the difficulty; if the difficulty warrants 500, I'll up the value for you.

Question by:klmorrison
    LVL 16

    Accepted Solution

    DECLARE @command varchar(8000),
          @dataType varchar(50),
          @dataValue varchar(50)

    SET @command = 'SELECT '

    FOR SELECT [Data Type], [Data Values] FROM Table

    OPEN curRows

    FROM curRows
    INTO @dataType, @dataValue


          SET @command = @command + '''' + @dataValue + ''' [' + @dataType + '], '

          FETCH NEXT
          FROM curRows
          INTO @dataType, @dataValue


    CLOSE curRows
    DEALLOCATE curRows

    SET @command = LEFT(@command, LEN(@command) - 1)

    EXEC (@command)
    LVL 13

    Expert Comment

    This shoud do it, but it totally depends on the fact that the select statement always return the records grouped
    ie   Name

    it doesnt really matter which one comes first as long as they are always grouped like that
    then this will work:

    create table #test (data_type varchar(50), data_value varchar(50) )
    INSERT #Test values ( 'Name', 'Tom Smith')
    INSERT #Test values ( 'Rank', 'Sergeant')
    INSERT #Test values ( 'Serial', '111')
    insert #tEST VALUES ( 'Rank', 'abc')
    insert #tEST VALUES ( 'Serial', '222')
    insert #tEST VALUES ( 'Name', 'MY Name')

    set nocount on
    -- create temp table for flipped table
    Create table #Rotate ( [Name] varchar(50) NULL , [Rank] varchar(50) NULL , [serial] varchar(50) NULL )
    declare @Data_Type varchar(50),
            @Data_value varchar(50),
          @counter  tinyint,
          @Insert  varchar(500),
          @data    varchar(1000),
          @SQL  varchar(1000)
    set @counter = 0
    set @Insert = 'INSERT #Rotate ( '
    SET @data  =  ' VALUES ( '
    set @SQL = ''
    declare tmpCursor CURSOR for
    select data_type, data_value  from #test
    open tmpCursor
    fetch next from tmpCursor into @Data_Type, @Data_value
    while @@fetch_status = 0
       -- assuming that the records are physically retrieved in Name, Rank, Serial  or any order therein , so Rank, Serial then Name
       -- but as soon as you get rank, rank, serial then things go haywire
       -- the constant is thus for every third loop
       If @counter = 3
            -- complete the sql string
              --- strip off the last comma )
              set @Insert = SUBSTRING( @Insert, 1, LEN(@Insert) - 1 )
            set @Data =   SUBSTRING( @Data   , 1, LEN(@Data) - 1 )
              -- add on the last bracket for the insert statment
              set @sql = @Insert + ') ' + @Data + ')'
            -- write the record
              EXEC (@SQL)
            -- reset the counter
            set @counter = 0
              -- reset the INSERT Statement
           set @Insert = 'INSERT #Rotate ( '
           SET @DATA  =  ' VALUES ( '
           set @SQL = ''

        -- build the sql string for inserting
        set @Insert = @Insert + @Data_Type + ','
        set @Data  = @Data  + '''' +  @Data_value + '''' + ','
        set @counter = @counter + 1
       fetch next from tmpCursor into @Data_Type, @Data_value
    close tmpCursor
    deallocate tmpCursor
    -- write the last record
    set @Insert = SUBSTRING( @Insert, 1, LEN(@Insert) - 1 )
    set @Data =   SUBSTRING( @Data   , 1, LEN(@Data) - 1 )
    -- add on the last bracket for the insert statment
    set @sql = @Insert + ') ' + @Data + ')'

    select * from #Rotate

    Author Comment

    Muzzy2003 and KarinLoos - thanks for your great answers, Muzzy2003 got it all right first try though.  Thanks for the quick response!  I upped the points to 350 for the quickness and accuracy :)

    LVL 16

    Expert Comment

    No problem. Thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now