SqlServer 列转行，用逗号,分割

最新推荐文章于 2024-07-26 14:20:58 发布

Ccccch丶

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量4k

点赞数 3

文章标签： sqlserver 数据库 sql

本文链接：https://blog.csdn.net/qq_15267865/article/details/127068127

版权

1.创建表单CREATE TABLE RIS_AcceptItems ( applyno INT, itemcode VARCHAR(100) )
2.插入数据
insert into RIS_AcceptItems values(959,'TJ56')
insert into RIS_AcceptItems values(959,'TJ58')
insert into RIS_AcceptItems values(1041,'TJ66')
insert into RIS_AcceptItems values(1041,'CH1352')
insert into RIS_AcceptItems values(1097,'CH1352')
insert into RIS_AcceptItems values(1097,'CH1378')
insert into RIS_AcceptItems values(1097,'CH1352')
insert into RIS_AcceptItems values(1097,'CH1378')
insert into RIS_AcceptItems values(1102,'CH1352')
insert into RIS_AcceptItems values(1102,'CH1378')
insert into RIS_AcceptItems values(1105,'CH1352')
insert into RIS_AcceptItems values(1105,'CH1378')
insert into RIS_AcceptItems values(1105,'CH1352')

3.查询数据 select applyno,stuff((select distinct ','+itemcode from RIS_AcceptItems where a.applyno=applyno for xml path('')),1,1,'') itemcode from RIS_AcceptItems a group by applyno

--查询结果
applyno   itemcode
959   TJ56,TJ58
1041   CH1352,TJ66
1097   CH1352,CH1378
1102   CH1352,CH1378
1105   CH1352,CH1378

4.查询的时候，用,作为分隔符，进行查询

DECLARE @FHCS VARCHAR(50)

set @FHCS = '1，2,3'
set @FHCS= REPLACE(@FHCS,'，',',') -- 将入参的中文分号转换成英文分号。统一类型
SELECT * FROM table A
inner JOIN [dbo].[fn_SplitTSQL](@FHCS,',') b on a.itemcode = b.ELEMENT
WHERE 1=1