We help IT Professionals succeed at work.

concatenate column names and values...

Hi,

I have a Stored Procedure which concatenates values from multiple columns and returns the result as one single column.

Now I need to have the table column names concatenated and be the first row in the result set.

Is it possible at all?? I am using SQL Server 2005..

Thanks,
Jayahar
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
you can concatenate the column  names in code and use UNION to return the combined result (column names  on the first row + column values)

SELECT col1+','+col2 + ','+ col3 as values
union
SELECT concatenatedvalues
CERTIFIED EXPERT
Top Expert 2012
Commented:
You should be able to use this system view for that:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableNameGoesHere'

Open in new window

Jesus RodriguezIT Manager
CERTIFIED EXPERT

Commented:
This will give it to you separated by space

SELECT
   STUFF(
     (
     SELECT
       ' ' + column_name
     FROM information_schema.columns
     WHERE table_name = 'YOUR_TABLENAME'
       ORDER BY ORDINAL_POSITION
     FOR XML PATH('')
     ), 1, 1, ''
   ) As concatenated_string