在SQLServer2005中，以逗号分隔的行形式返回多列

Question

我很好奇这是否可能。

我有一个表，或者这可以特定于任何有数据的旧表。一个简单的SELECT将返回列和行作为结果集。我试图找出的是，是否可以返回行而不是列，这些列是串联的，并且是逗号分隔的。因此，返回的行数是预期的，但只有一个varchar列包含所有列的逗号分隔结果，就像CSV文件一样。

谢谢。

[更新]

以下是我询问的更多细节。我没有在客户端上执行此操作的选项，这是我正在尝试使用SSIS执行的任务。

场景：我有一个在SSIS中动态创建的表，但每次生成时列名都会更改。原始包使用BCP获取数据并将其放入平面文件中，但由于作为作业运行时具有权限，BCP无法在所需的目标创建平面文件。我们也无法改变这一点。

另一个问题是，对于SSIS 2005，使用平面文件目标，必须从输入源映射列名，而我无法这样做，因为列名一直在更改。

我已经编写了一个脚本任务，从原始表中获取所有数据，然后使用流写入器写入CSV，但我必须遍历每一行，然后遍历每一列，以生成由所有列组成的字符串。我想用vb.net来衡量sql server上这种列串联的性能，以对抗讨厌的循环

如果我能让sql为每一行生成一列，我就可以在文本文件中写一行，而不是遍历每一列来构建行。

Answer 1

我认为你应该试试这个

SELECT UserName +','+ Password AS ColumnZ
FROM UserTable

Answer 2

假设你知道表中有哪些列，并且你不想做一些动态和疯狂的事情，你可以做这个

SELECT CONCAT(ColumnA, ',', ColumnB) AS ColumnZ
FROM Table

Answer 3

使用SQL Server的XML函数有一种奇特的方法，但对于初学者来说，您可以将关心的列的内容强制转换为varchar并用逗号连接吗？

SELECT cast(colA as varchar)+', '+cast(colB as varchar)+', '+cast(colC as varchar)
FROM table

请注意，如果您的任何内容中有逗号或双引号，这将被绊倒，在这种情况下，您还可以在每个强制转换中使用替换函数来转义它们。

Answer 4

这可能需要清理一些，但您可以通过使用存储在sys.objects和sys.columns中的元数据以及动态SQL来做到这一点。请注意，我不是动态SQL的粉丝，但出于报告目的，这应该不会有太大问题。

一些SQL来创建测试数据：

if (object_id('test') is not null)
drop table test;
create table test
(
id uniqueidentifier not null default newId()
,col0 nvarchar(255)
,col1 nvarchar(255)
,col2 nvarchar(255)
,col3 nvarchar(255)
,col4 nvarchar(255)
);
insert into test (col0,col1,col2,col3,col4)
select 'alice','bob','charlie','dave','emily'
union
select 'abby','bill','charlotte','daniel','evan'

构建CSV行的存储过程：

-- emit the contents of a table as a CSV.
-- @table_name: name of a permanent (in sys.objects) table 
-- @debug: set to 1 to print the generated query
create procedure emit_csv(@table_name nvarchar(max), @debug bit = 0)
as
declare @object_id int;
set nocount on;
set @object_id = object_id(@table_name);
declare @name nvarchar(max);
declare db_cursor cursor for
select name 
from sys.columns 
where object_id = @object_id;
open db_cursor;
fetch next from db_cursor into @name
declare @query nvarchar(max);
set @query = '';
while @@FETCH_STATUS = 0
begin
-- TODO: modify appended clause to escape commas in addition to trimming
set @query = @query + 'rtrim(cast('+@name+' as nvarchar(max)))'
fetch next from db_cursor into @name;
-- add concatenation to the end of the query. 
-- TODO: Rearrange @query construction order to make this unnecessary
if (@@fetch_status = 0)
set @query = @query + ' + '','' +'
end;

close db_cursor;
deallocate db_cursor;
set @query = 'select rtrim('+@query+')  as csvrow from '+@table_name;
if @debug != 0
begin
declare @newline nvarchar(2);
set @newline = char(13) + char(10)
print 'Generated SQL:' + @newline + @query + @newline + @newline;
end
exec (@query);

对于我的测试表，这将生成查询：

select 
rtrim(rtrim(cast(id as nvarchar(max))) 
+ ',' 
+rtrim(cast(col0 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col1 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col2 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col3 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col4 as nvarchar(max))))  
as csvrow 
from test

结果集：

csvrow
-------------------------------------------------------------------------------------------
EEE16C3A-036E-4524-A8B8-7CCD2E575519,alice,bob,charlie,dave,emily
F1EE6C84-D6D9-4621-97E6-AA8716C0643B,abby,bill,charlotte,daniel,evan

建议

1. 修改光标循环以转义逗号
2. 确保@table_name引用存储过程中的有效表(if object_id(@table_name) is null)
3. 一些异常处理会很好
4. 设置权限，这样只有运行报表的帐户才能执行它。动态SQL中的字符串串联可能是一个很大的安全漏洞，但我看不到其他方法可以做到这一点
5. 一些错误处理可以确保光标被关闭和释放，这可能很好

这可以用于任何不是#temp表的表。在这种情况下，您必须使用tempdb..中的sys.objects和sys.columns。

Answer 5

select STUFF((select ','+ convert(varchar,l.Subject) from tbl_Student B,tbl_StudentMarks L
where B.Id=L.Id FOR XML PATH('')),1,1,'') Subject FROM tbl_Student A where A.Id=10