x

Get column names appended to results

Hello,

I was looking to find a way to append the column names to each row. there are over 100 columns so I cannot hardcode it.

alt text

alt text

Thank You !

more ▼

asked May 01, 2013 at 05:40 PM in Default

avatar image

IT1
388 27 33 37

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Hi you can take use of the meta tables under the schema sys,I found one solution like:

DECLARE @tbName NVARCHAR(MAX) DECLARE @colName NVARCHAR(MAX) DECLARE @sql NVARCHAR(MAX)

SET @tbName = 'MyTable' SET @colName = (SELECT '''' + NAME + '&''+CONVERT(NVARCHAR(100),' + NAME + ') AS ' + NAME + ',' FROM SYS.ALL_COLUMNS WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.ALL_OBJECTS WHERE NAME = @tbName) FOR XML PATH(''))

SET @colName = STUFF(@colName, LEN(@colName), 1, '') -- remove the last ',' sign SET @colName = REPLACE(@colName, '&','&') -- unenscape the & sign

PRINT @colName

SET @sql = 'SELECT ' + @colName + ' FROM ' + @tbName

PRINT @sql

EXEC SP_EXECUTESQL @SQL

enjoy!

more ▼

answered May 05, 2013 at 06:25 AM

avatar image

mikedeng
91 1 3 4

(comments are locked)
10|1200 characters needed characters left

Adding to mikedengs answer; FOR XML PATH will return special characters (&, < and so on) as encoded values (&amp;, &lt; and so on).

To get only the & in your result, add TYPE in your for xml path-statement like this:

 FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'), 1, 1, '') 

See here for more info

more ▼

answered May 06, 2013 at 01:07 PM

avatar image

larsts
20

(comments are locked)
10|1200 characters needed characters left

The only way I know to do something like this would to program it into the query:

 SELECT 'ID&'+ID,
 'Date$'+CAST(Date as VARCHAR(10)),
 'NAme$'+Name
 FROM MyTable;

Realizing you have 100 columns makes this difficult.

more ▼

answered May 02, 2013 at 08:32 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

You could do it with a dynamic query against syscolumns, but I'm busy trying to learn about indexes right now...

May 02, 2013 at 10:11 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1090

asked: May 01, 2013 at 05:40 PM

Seen: 909 times

Last Updated: May 07, 2013 at 01:48 AM

Copyright 2017 Redgate Software. Privacy Policy