如何用逗号分隔将一列的行数据写入一行 [英] how to write row data of a column into one row with coma separated

问题描述

以以下格式编写选择查询时，我有我的表数据

1 jks abc
1个kjs bcde
2 hjd rty

现在如何获取数据

1 jks abc，bcde
1个kjs abc，bcde
2 hgd rty


在此先感谢

i have my table data when writing the select query in the below format

1 jks abc
1 kjs bcde
2 hjd rty

now how can i get the data

1 jks abc,bcde
1 kjs abc,bcde
2 hgd rty


Thanks in advance

推荐答案

请参见以下示例

see below example

Input Table                 Output Table
------------                --------------
a       b                   a     b
------------                --------
1	a                   1	  a,b
1	b	------>     2	  c
2	c        Query 

select a,substring(b,0,Len(b)) as b from
(
	select distinct a,
	(
		select b + ',' as [text()] from 
		(
			select 1 as a,'a' as b
			union all
			select 1 as a,'b' as b
			union all
			select 2 as a,'c' as b
		) as tbl_a where tbl_a.a=tbl_b.a
		for xml path('')	
	) as b

	from
        (
		select 1 as a,'a' as b
		union all
		select 1 as a,'b' as b
		union all
		select 2 as a,'c' as b
	) as tbl_b
)
as tbl_c

按照下面的步骤根据您的表结构修改上述查询
将下划线部分替换为您的表名
a =您的列名，例如id
b =您要与''，''
组合的列名
另一个例子
如何获取单行从多行的表中 [

follow steps given below for modifying above query as per your table structure
replace underline section with your table-name
a=your columnname e.g. id
b=column name which you want to combine with '',''

another example
how to get a single row from a table from multiple rows[^]

Happy Coding!
:)

这篇关于如何用逗号分隔将一列的行数据写入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！