SQL Column Names and Values

Question

I need to turn this table:

RecID   Date        Time        FirstName   LastName
6       5/28/2013   9:50:07 AM  Jenny       Welhberg

Into this:

Column      Value
RecID       6
Date        5/28/2013
Time        9:50:07 AM
FirstName   Jenny
LastName    Welhberg

I have:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename'

select * from tablename where recid=6

Also note that I am working on SQL Server 2000 for this report.

Answer 1

Well, you have a problem, possibly. The columns seems to have different types.

Let me ignore that. Let me assume that they all have the same type. In "2000" you don't have unpivot. I think the fastest way is to do a cross join:

select cols.colname,
       (case when cols.colname = 'RecId' then RecId
             when cols.colname  = 'Date' then Date
             when cols.colname  = 'Time' then Time
             when cols.colname  = 'FirstName' then FirstName
             when cols.colname  = 'LastName' then LastName
        end) as Value
from t cross join
     (select 'RecID' as colname union all
      select 'Date' union all
      select 'Time' union all
      select 'FirstName' union all
      select 'LastName'
     ) cols

If the original types are not strings, then you might have to convert them.