将单行的n列值连接成逗号分隔的字符串 [英] Concatenate n column values for a single row into a comma-separated string

问题描述

我有多个表，我正在尝试快速记录存储过程。



我知道如何将表的列变为CSV字符串：

 声明  @ columnList   varchar （ 8000 ）
  select  @columnList = stuff（（选择 ' ，' + COLUMN_NAME 
 来自 INFORMATION_SCHEMA.COLUMNS 
 其中 TABLE_NAME = @ currentTable 
 订单 按 ORDINAL_POSITION 
  for  xml路径（' '）），
  1 ， 1 ，' '）
 
 选择  @ columnList  

结果为：

col1，col2，col3，...，colN





但是，现在我想用一行数据做同样的事情。例如：

 ++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + + + + + + + + + + + + + + + + + + + + b ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > 
 
 
 
我希望所有值都显示为：
 
  Val1，Val2，Val3，.. 。，ValN  
 
 
 
这可能吗？
 
 
 
 -Wesley 
解决方案
 
 
 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（nvar） char，['+ COLUMN_NAME +']，120），''''）+''''''
 WHEN DATA_TYPE ='nvarchar'或DATA_TYPE ='varchar'OR DATA_TYPE ='nchar'OR DATA_TYPE = 'char'
那么'''''+ COALESCE（更换（['+ 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）
 
 
 
而不是逗号，在字符串前面加上SELECT，用+连接名称，添加FROM MyTable部分，然后在结果字符串上使用EXEC。
 
 
请先阅读我的评论。 
 
 
将数据从SQL服务器数据库导出到csv文件的最简单方法是使用 bcp实用程序 [ ^ ]。
 
 创建格式文件（SQL Server） [ ^ ] 
 
 指定字段和行终止符 [ ^ ] 
 
 
 
 
 
 MSDN写道：
 
 
 
 
 
 bcp AdventureWorks.HumanResources.Department out C：\ myDepartment-ct.txt -c -t ，-r \ n -T 
 
 
 
 
这会创建 Department-ct.txt ，包含16条记录，每条记录包含四个字段。字段用逗号分隔。
 
 
 
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:

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
解决方案
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)

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.

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:




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.


                        
这篇关于将单行的n列值连接成逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！