question

Joe_Hell avatar image
Joe_Hell asked

TSQL to return columns as values

Want to return column names as values and that is not a very good explanation so seomthing like this select cast(getdate() as date)as date, cast(getdate() as datetime2)as datetime2, cast(getdate() as datetime)as datetime, cast(getdate() as datetimeoffset)as datetimeoffset, cast(getdate() as smalldatetime)as smalldatetime returned in a spreadsheet like format. I came up with the following which I know is not very elegant. drop table #time go create table #time (A varchar(50), B varchar(50)) insert into #time values ('date', cast(getdate() as date)) insert into #time values ('datetime2', cast(getdate() as datetime2)) insert into #time values ('datetime', cast(getdate() as datetime)) insert into #time values ('datetimeoffset', cast(getdate() as datetimeoffset)) insert into #time values ('smalldatetime', cast(getdate() as smalldatetime)) Select A as [Time Type], B as Value from #Time any reasonable suggestions welcome. TIA,
tsqlcode
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
You will probably want to look at the PIVOT/UNPIVOT command, depending on what version of SQL Server you are using. For SQL Server 2008 (just as a starting point), you can look here: http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.100%29.aspx I believe that PIVOT/UNPIVOT works with 2005+. Hope this helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
It depends on why you are trying to do this. Pivot may very well be the way to go, as John suggested. But that is mostly designed to, well, pivot all the data. If you want to include the column names but otherwise have the normal result set, it may be less optimal. Another option, assuming that all of your columns are able to hold text data, is to UNION in the column names. For instance: select 'col1' as col1, 'col2' as col2, 'col3' as col3 UNION select cast(col1 as varchar), cast(col2 as varchar), cast(col3 as varchar) from theTable Depending on what your ultimate goal is, you may not need to do that though. You said you want a spreadsheet like format, but most programs that produce a spreadsheet from SQL, including the built-in Wizard, give you something along the lines of a check-box to include the column names as the first row. If you are trying to interface with SQL through another programming language, the column names are normally available along with any query. Using [pyodbc][1] for instance, the cursor object that stores the result set will also have a description attribute that gives the column data. [1]: http://code.google.com/p/pyodbc/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
+1, excellent points.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.