如何在sql server 2008中将行转换为逗号分隔值? [英] how to convert rows into comma separated values in sql server 2008?
本文介绍了如何在sql server 2008中将行转换为逗号分隔值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张桌子像
create table test(id int )
insert into test( 101 ),( 102 ),( 103 ).....
现在我的预期输出是:
测试
101,102,103
我知道一种方法来解决我的问题:
decare @ id varchar ( 100 )
选择 @ id = coalsce( @id ,' ')+ cast(id as varchar )来自 test
选择 @ test
但是上面的方法不适合我的要求
所以我需要用正常的select语句解决它,即不使用声明的变量
解决方案
尝试
创建 < span class =code-keyword> table #test(id int )
insert into #test(id) VALUES ( 101 )
insert into #test(id) VALUES ( 102 )
insert into #test(id) VALUES (103 )
SELECT SUBSTRING(
(
< span class =code-keyword> SELECT ' ,' + CAST(Id < span class =code-keyword> AS VARCHAR ) FROM #Test
FOR XML PATH(' ') ), 2 , 10000 ) AS Csv
DROP TABLE #test
在逗号分隔列表中选择值的最简单方法可能是使用FOR XML子句。例如,看一下这篇文章:以列逗号分隔字符串获取列值 [ ^ ]。
如果您需要更多功能,甚至可以考虑创建自定义聚合。请参阅 SQL Server中的自定义聚合中的使用多个参数,连接一章 [ ^ ]
我的实际要求是不同的
最后我通过上面的例子解决了
我的最终解决方案是
选择
LEFT (SubGroupId,LEN(SubGroupId) - 1 )
来自
(
选择
CONVERT ( varchar (max),
(
选择 SubGroupId as [ text ()],' ,' as [ text ()]
来自 CMSClientSubGroup csg WITH ( NOLOCK )
INNER JOIN USER_ACCOUNT ua WITH ( NOLOCK )
- on u.user_id = ua.user_id
- INNER JOIN CMSClientSubGroup csg WITH( NOLOCK)
on csg.SubGroupId = ua.account_id
- 在哪里ua.USER_ID = 1
xml路径(' ')
)
) as result
)s
hi ,
I have one table like
create table test(id int)
insert into test(101),(102),(103).....
now my expected output is:
test
101,102,103
I know one method to resolve my problem :
decare @id varchar(100)
select @id=coalsce(@id,'')+cast(id as varchar) from test
select @test
but the above method is not suitable to my requirement
so i need to resolve it with normal select statement i.e without using declared variable
解决方案
Try
create table #test(id int) insert into #test(id) VALUES (101) insert into #test(id) VALUES (102) insert into #test(id) VALUES (103) SELECT SUBSTRING( ( SELECT ',' + CAST(Id AS VARCHAR) FROM #Test FOR XML PATH('')), 2,10000) AS Csv DROP TABLE #test
Probably the easiest way to select values into a comma separated list is to use FOR XML clause. For example have a look at this article: Get Column values as comma seperated string [^].
If you need a lot more functionality, you can even consider creating a custom aggregate. See chapter Using Multiple Parameters, Concatenate in Custom Aggregates in SQL Server[^]
my actual requirement is differnet
finaly i solved by taking the above examples
my final solution is
select LEFT(SubGroupId ,LEN(SubGroupId ) - 1) from ( select CONVERT(varchar(max), ( select SubGroupId as [text()], ',' as [text()] from CMSClientSubGroup csg WITH(NOLOCK) INNER JOIN USER_ACCOUNT ua WITH(NOLOCK) -- on u.user_id=ua.user_id --INNER JOIN CMSClientSubGroup csg WITH(NOLOCK) on csg.SubGroupId=ua.account_id --WHERE ua.USER_ID =1 for xml path('') ) )as result ) s
这篇关于如何在sql server 2008中将行转换为逗号分隔值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文