如何将选择中的所有列与 SQL Server 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13761337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to concatenate all columns in a select with SQL Server
提问by рüффп
I need my select to have a pattern like this:
我需要我的选择有这样的模式：
SELECT '<text> ' + tbl.* + ' </text>' FROM table tbl;
The ideal solution would have all the columns separated by a comma in order to have that output:
理想的解决方案是将所有列以逗号分隔，以便获得该输出：
SQL result for Table 1 with two columns:
表 1 的 SQL 结果有两列：
'<text>col1, col2</text>'
SQL result for Table 2 with three columns:
表 2 的 SQL 结果，包含三列：
'<text>col1, col2, col3</text>'
I tried to use the CONCAT(...)function like this:
我尝试使用这样的CONCAT(...)功能：
SELECT CONCAT('<text>', tbl.*, '</text>')
FROM table2 tbl
But I understand it is not so simple because the variable number of columns.
但我明白它不是那么简单，因为列数是可变的。
Is there any simple solution to address that problem?
有没有简单的解决方案来解决这个问题？
I am using SQL Server 2008 R2.
我正在使用 SQL Server 2008 R2。
回答by Kaf
Any number of columns for a given tablename;
If you need column nameswrapped with <text>
给定 tablename 的任意数量的列；如果你需要用包裹的列名<text>
DECLARE @s VARCHAR(500)
SELECT @s = ISNULL(@s+', ','') + c.name
FROM sys.all_columns c join sys.tables t
ON c.object_id = t.object_id
WHERE t.name = 'YourTableName'
SELECT '<text>' + @s + '</text>'
SQL FiddleExample here
SQL小提琴示例在这里
-- RESULTS
<text>col1, col2, col3,...</text>
If you need select query result set wrappedwith <text>then;
如果需要用then包裹的select 查询结果集<text>；
SELECT @S = ISNULL( @S+ ')' +'+'',''+ ','') + 'convert(varchar(50), ' + c.name FROM
sys.all_columns c join sys.tables t
ON c.object_id = t.object_id
WHERE t.name = 'YourTableName'
EXEC( 'SELECT ''<text>''+' + @s + ')+' + '''</text>'' FROM YourTableName')
SQL FiddleExample here
SQL小提琴示例在这里
--RESULTS
<text>c1r1,c2r1,c3r1,...</text>
<text>c1r2,c2r2,c3r2,...</text>
<text>c1r3,c2r3,c3r3,...</text>
回答by Mikael Eriksson
MS SQL Server 2008 Schema Setup:
MS SQL Server 2008 架构设置：
create table YourTable
(
ID int identity primary key,
Name varchar(50),
)
insert into YourTable values
('Name 1'),
('Name 2'),
('Name 3'),
('Name 4'),
('Name 5')
Query 1:
查询 1：
select (
select (
select ', '+T2.N.value('./text()[1]', 'varchar(max)')
from (
select T.*
for xml path(''), type
) as T1(N)
cross apply T1.N.nodes('/*') as T2(N)
for xml path(''), type
).value('substring(./text()[1], 3)', 'varchar(max)')
for xml path('text'), type
)
from YourTable as T
结果：
| COLUMN_0 |
--------------------------
| <text>1, Name 1</text> |
| <text>2, Name 2</text> |
| <text>3, Name 3</text> |
| <text>4, Name 4</text> |
| <text>5, Name 5</text> |
回答by bummi
I tried a more genericapproach
我尝试了更通用的方法
Create Procedure P_GetConcatColumns(@tablename varchar(200),@Seperator Varchar(20),@StartTag Varchar(20),@EndTag Varchar(20),@WhereString Varchar(400),@OrderString Varchar(400)) as
DECLARE @TmpTableName VARCHAR(100),
@Columns NVARCHAR(4000),
@SQL NVARCHAR(MAX),
@GENNAME VARCHAR(100)
Select @GENNAME='##'+Replace(Cast(NewID() as Varchar(40)),'-','')
IF OBJECT_ID('tempdb.dbo.' + @GENNAME) IS NOT NULL
BEGIN
EXEC('DROP TABLE ' +@GENNAME);
END
Select @SQL='SELECT TOP 1 * INTO '+@GENNAME+' FROM ' + @tablename
Exec (@SQL)
SET @TmpTableName = 'tempdb.dbo.' + @GENNAME
SELECT @Columns =
STUFF(
(
SELECT '+' + @Seperator + '+CAST(Coalesce(' + c.name + ','''') AS VARCHAR(MAX))' + CHAR(13)
FROM tempdb.sys.columns c
WHERE c.[object_id] = object_Id(@TmpTableName)
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,len(@Seperator)+2,'')
--SET @SQL = N'SELECT ' +''''+ @StartTag +''''+'+'+ @Columns +''''+'+'+ @EndTag + ''' FROM ' + @tablename
SET @SQL = N'SELECT ''' +@StartTag+ '''+'+ @Columns +'+'''+ @EndTag + ''' FROM ' + @tablename
+' '+Coalesce(@WhereString,'')
+' '+Coalesce(@OrderString,'')
Print @SQL
EXEC sp_executeSQL @SQL
--ExampleCall P_GetConcatColumns 'Arten',''',''','<test>','</test>','where id>1','Order by ID desc'
回答by eyeshield21
Try the code below:
试试下面的代码：
SELECT Stuff(
(SELECT N', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'YourTableName' FOR XML PATH(''),TYPE)
.value('text( [1]','nvarchar(max)'),1,2,N'')
