"Rotate" Tables in SQL Server 2000

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 + '], '

      FROM curRows
      INTO @dataType, @dataValue


CLOSE curRows

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

EXEC (@command)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
klmorrisonAuthor Commented:
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 :)

No problem. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.