Concatenate n column values for a single row into a comma-separated string

Question

3.00/5 (2 votes)

See more:

SQL-Server-2005

I have multiple tables that I am attempting to log quickly for a stored procedure.

I know how to get the columns for the table into a CSV string:

SQL

declare @columnList varchar(8000)
select @columnList=stuff( (select ','+COLUMN_NAME
				from INFORMATION_SCHEMA.COLUMNS
				where TABLE_NAME=@currentTable
				order by ORDINAL_POSITION
				for xml path('')),
				1,1,'')

select @columnList

Resulting as:
col1,col2,col3,...,colN

However, now I want to do the same thing with a single row of data. Example:

+++++++++++++++++++++++++++++++++++++++++++
+UnitID+ Col1 + Col2 + Col3 + ... + Col N +
+++++++++++++++++++++++++++++++++++++++++++
+  ID  + Val1 + Val2 + Val3 + ... + Val N +
+++++++++++++++++++++++++++++++++++++++++++

I want all of the values to show up as:
Val1,Val2,Val3,...,ValN

Is this possible?

-Wesley

Posted 3-Dec-13 5:58am

Member 10442363

Add a Solution

Comments

Maciej Los 3-Dec-13 12:14pm

Not sure what kind of issue do you have, Wesley...

4 solutions

Solution 1

Instead of commas, prefix the string with "SELECT ", concatenate the names with "+", add the "FROM MyTable" part and then use EXEC on the resulting string.

Posted 3-Dec-13 6:11am

OriginalGriff

Solution 2

Please, read my comment first.

The simplest way to export data from SQL server database into csv file is to use bcp utility[^].
Create a Format File (SQL Server)[^]
Specifying Field and Row Terminators[^]

MSDN wrote:

SQL
bcp AdventureWorks.HumanResources.Department out C:\myDepartment-c-t.txt -c -t, -r \n -T
This creates Department-c-t.txt, which contains 16 records with four fields each. The fields are separated by a comma.

Posted 3-Dec-13 6:21am

Maciej Los

Comments

Member 10442363 4-Dec-13 9:13am

Thank you for your help; however, I am attempting to do this as a called procedure inside the database.

Maciej Los 4-Dec-13 10:19am

You're welcome ;)

Solution 4

U need to use the select statement with all column names concatenated with the '+' sign and use cast to varchar before any numeric or datetype field.

Posted 3-Dec-13 17:33pm

Jain Nishant

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Vaselenko · Accepted Answer · 2013-12-03T06:26:00

Solution 3

DECLARE @columnList VARCHAR(8000)

SELECT @columnList = stuff((
	SELECT ',' + COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @currentTable
	ORDER BY ORDINAL_POSITION
	FOR XML path('')
	), 1, 1, '')

DECLARE @valueList VARCHAR(8000)

SELECT @valueList = stuff((
	SELECT '+'',''+ ' + CASE
		WHEN DATA_TYPE = 'datetime' THEN '''"''+COALESCE(CONVERT(nvarchar, [' + COLUMN_NAME + '], 120),'''')+''"'''
		WHEN DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar' OR DATA_TYPE = 'nchar' OR DATA_TYPE = 'char'
			THEN '''"''+COALESCE(REPLACE([' + COLUMN_NAME + '],''"'',''""''),'''')+''"'''
		ELSE 'COALESCE(CAST([' + COLUMN_NAME + '] AS nvarchar),'''')'
		END
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @currentTable
	ORDER BY ORDINAL_POSITION
	FOR XML path('')
	), 1, 5, '')

SET @valueList = 'SELECT ' + @valueList + ' AS [' + @columnList + '] FROM ' + @currentTable

--PRINT @valueList
EXEC (@valueList)

Posted 3-Dec-13 6:26am

Sergey Vaselenko

Updated 4-Dec-13 5:52am

v2

Comments

Member 10442363 4-Dec-13 10:11am

I have modified this to show:

declare @valueListSQL varchar(8000)
select @valueListSQL = stuff( (select
case when DATA_TYPE = 'datetime' then '+'',''+COALESCE(convert(nvarchar,['+COLUMN_NAME+'],120),''null'')'
else '+'',''+COALESCE(CAST(['+COLUMN_NAME+'] AS varchar),''null'')' end
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@currentTable-- and DATA_TYPE='datetime'
order by ORDINAL_POSITION
for xml path('')),
1,5,'')

declare @valueList varchar(8000)
set @valueList = 'SELECT ' + @valueListSQL + ' FROM ' + @currentTable + ' where fkpmunit = '+ cast(@unitToDrop as varchar(9))
select @valueList
exec (@valueList)

However, I need to have quotation marks around datetime, char, nvarchar, and varchar data types. How do I add them?

Member 10442363 4-Dec-13 11:36am

[UPDATE]
I have modified this to handle multiuple data types, but I still can't get quotes around the specific ones as I listed above:

declare @valueListSQL varchar(8000)
select @valueListSQL = stuff( (select
case when ORDINAL_POSITION=1 then 'COALESCE(CAST(['+COLUMN_NAME+'] as varchar),''null'')+'','''
when ORDINAL_POSITION<>1 and DATA_TYPE = 'datetime' then '+COALESCE(convert(nvarchar,['+COLUMN_NAME+'],120),''null'')+'','''
--'+'',''+COALESCE(convert(nvarchar,['+COLUMN_NAME+'],120),''null'')'
when ORDINAL_POSITION<>1 and DATA_TYPE in ('char','nvarchar','varchar') then '+COALESCE(['+COLUMN_NAME+'],''null'')+'','''
else '+COALESCE(CAST(['+COLUMN_NAME+'] as varchar),''null'')+'','''
end --'+'',''+COALESCE(CAST(['+COLUMN_NAME+'] AS varchar),''XnullX'')'+'' end
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@currentTable --and DATA_TYPE='datetime'
and COLUMN_NAME in ('pk','StartDate','fkpmUnit','DidViolate40PctRuleWhenHoused')
order by ORDINAL_POSITION
for xml path('')),
1,0,'')

select @valueListSQL VLSQL
declare @valueList varchar(8000)
set @valueList = 'SELECT ' + LEFT(@valueListSQL,LEN(@valueListSQL)-4) + ' FROM ' + @currentTable + ' where fkpmunit = '+ cast(@unitToDrop as varchar(9))
select @valueList VLfinalSQL
exec (@valueList)

OUTPUT:
[@VQSQL] COALESCE(CAST([PK] as varchar),'null')+','+COALESCE(CAST([fkpmUnit] as varchar),'null')+','+COALESCE(convert(nvarchar,[StartDate],120),'null')+','+COALESCE([DidViolate40PctRuleWhenHoused],'null')+','
[@VQfinalSQL] SELECT COALESCE(CAST([PK] as varchar),'null')+','+COALESCE(CAST([fkpmUnit] as varchar),'null')+','+COALESCE(convert(nvarchar,[StartDate],120),'null')+','+COALESCE([DidViolate40PctRuleWhenHoused],'null') FROM pmUnitResident where fkpmunit = 123281
[FINAL OUTPUT] 105347,123281,2006-08-01 00:00:00,No

Sergey Vaselenko 4-Dec-13 11:56am

I have updated the code.
I've also added the REPLACE function to qoute the qoutes.

Why are you specify null values as "null" in the output?

Member 10442363 4-Dec-13 14:10pm

Actually, I am attempting to log all of the entires in the row of a table so that if I need to rollback a previously commited deletion that it's an easy SQL statement to execute.