Columns as rows and rows as columns?

[Hurricane766]

Hi,
I wondering how to do this, and if this is possible:

Say I have a table in my database:

Table t1:

col1 | col2 | col3
------------------
r1v1 | r1v2 | r1v3


And what I want to get is something like:

x | y
---------------
col1 | r1v1
col2 | r1v2
col3 | r1v3


...almost exactly like flipping the table diagonally. I don't care what the new column names (x and y) are and this will only be done one row at a time.

I can get all the column names like this:

select Cname from syscolumns where Tname = <table_name>;

But I'm unsure of how to add another column that would be selecting the value, from the table Tname, from the column named Cname for a specific row.

Does anyone have any ideas? I need this to work for many different tables.


Thanks for any help

 

[PHV]

Provided col1, col2 and col3 have the same data type:
SELECT 'col1' x, col1 y FROM t1
UNION SELECT 'col2', col2 FROM t1
UNION SELECT 'col3', col3 FROM t1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hurricane766]

Thanks again PH