"Rotate" Tables in SQL Server 2000
Posted on 2004-11-18
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
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.