SQL Question

[Phaneuf3]

I'll do my best to simplify my question... It seems like it should be fairly simple but it's Friday afternoon and my brain just isn't working right...

If I have a very simple SQL query to return a single row based on it's index to get something like this:

SELECT * FROM Table1 WHERE Index = 1


Index   A     B     C    D     Z
------------------------------
1         1    2     3     4      26

Is there any simple way I can alter my query to have it return like this:

Column Name       Value
-----------------------
A                        1
B                        2
C                        3
D                        4
Z                        26

[Devils'Advocate]

Not directly in an SQL statement. You would need to write some dynamic SQL that uses the system tables. What engine do you have? MS-SQL?

[Phaneuf3]

Originally Posted by devils'advocate

not directly in an sql statement. You would need to write some dynamic sql that uses the system tables. What engine do you have? Ms-sql?

ms-sql (2008)

The only way I keep coming back to is a series of union statements which gets really ugly really fast.

[Devils'Advocate]

What are you unioning together? Are you using sysobjects/syscolumns to get the column names?

I'd make a cursor loop and build the dynamic sql statement.....
let @dynamicsql = 'SELECT '
for each column in tablex
get @columnname
let @dynamicsql = @dynamicsql + @columnname + ' (select ' + @columnname + ' from tablex where id = 1), '
next
remove last comma
let @dynamicsql = @dynamicsql + 'from tablex where id = 1'
exec (@dynamicsql)