Row Col Transposition in SQL Server 2005/2000

Question

In SQL Server 2005, I have a table say tbl_Info. There I have one column say Info.

The values are as under

Name1
Age1
Sex1
Name2
Age2
Sex2
.....
.....
.....

And the row values goes on like this. My question is how to make row col transposition. The desired output will be

Name      Age        Sex

Name1      Age1      Sex1
Name2     Age2       Sex2

.....     ....     ........
.....     ....      ........

You can use a table variable or temp table.

And please don't give the solution by using assemblies. Because I already did that by using assemblies but I want a solution which I can even use in SQL Server 2000.

Answer 1

You want to pivot the table. Normally you want at least two columns, though (one for a key and one for a value) and you also generally need to know in advance what columns you are expecting in the results.

Answer 2

The general idea is that you need another column to guarantee order -- that's typically going to be a primary key of some integer type, but to keep things simple say we just use:

CREATE TABLE foo (ord INT, col VARCHAR)

with ord just inserted as 0, 1, 2, etc.

Now the SELECT you want is:

SELECT a.col AS Name, b.col AS Age, c.col AS Sex
  FROM foo AS a
  JOIN foo AS b ON(b.ord=1+a.ord)
  JOIN foo AS c ON(c.ord=2+a.ord)
  WHERE a.ord%3=0

which will work on just about _any_thing that dares call itself "SQL";-).

For example, when the data are:

  ord   col
    0  John
    1    23
    2     M
    3  Mary
    4    21
    5     F
    6  Karl
    7    25
    8     M

the above SELECT's result is:

 Name   Age   Sex
 John    23     M
 Mary    21     F
 Karl    25     M