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.



Answers

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


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.



If you require an artifical ordering (an ordering you cannot create dynamicaly by sorting on some columns), you have to add a Position column. When you then reorder two rows, you have to change all rows between them if you use a dense coding.

You have the option to use a lose coding - for example the first row gets position 100, the second 200, the third 300, and so on. This will increase the complexity of your logic but allow you to perform a number of reordering operations without the need to modify more than one row.



This video can help you solving your question :)
By: admin