Building a table from SQL Query

I am hoping someone can point me in the right direction with this.

I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:

==============================
Column Name || Column Value
------||------
Column Name || Column Value
------||------

I hope I have explained myself properly. Any help would be greatly appreciated.

Do you want to transpose your data from row to column?

If this is what you want, it depends on which version of SQL server you are using. In SQL Server 2005, there is a PIVOT function which does this knid of job. In 2000, you can use CASE statement to construct a customized solution.

If you need help on this, you can post some sample data for others to look at. Plus the final result you are expecting.

|||Unless I misunderstood, PIVOT is not quite what I am looking for.

What I need is to take the name of the column that a field is in and use that as a header for table.

Any help would be greatly appreciated.

GKC|||

Could you test this out: (instead of PIVOT, use UNPIVOT)

step 1:

create table usingunpivot2005
(
myID varchar(10) primary key,
col1 varchar(50),
col2 varchar(50),
col3 varchar(50)
)
insert into usingunpivot2005 (myID, col1, col2, col3)
values('myid01','40','20','30')

Step 2:

with switchCTE as(
select cast(colname as varchar(5)) as colname, value
from usingunpivot2005 p

UNPIVOT
(value for colname in (col1, col2, col3)) as unpvt)
select *
from switchCTE

You should know your columns' name. If the value in that column is NULL, that column will be skipped.

|||SELECT ColName, MIN(CASE WHEN P.myID = 'myid01' THEN col1 END) AS Value
FROM
(SELECT 'Col 1' as ColName, myID, col1 FROM usingunpivot2005
UNION
SELECT 'Col 2', myID, col2 FROM usingunpivot2005
UNION
SELECT 'Col 3', myID, col3 FROM usingunpivot2005) P
GROUP BY ColName
ORDER BY ColName