[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


"Rotate" Tables in SQL Server 2000

Posted on 2004-11-18
Medium Priority
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
  • 2
LVL 16

Accepted Solution

muzzy2003 earned 1400 total points
ID: 12615709
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)
LVL 13

Expert Comment

ID: 12616215
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

ID: 12616421
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

ID: 12616441
No problem. Thanks.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 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