菜单

案例一、根据逗号分割转换列表

1.1 数据准备

select * 
into #tempA 
from (
	select '1' id,'1,2,3,4,5' name
	union 
	select '2','2,3,4,5,6'
) aa

基础技术、技术与框架SQLServer 以逗号分割的字符串转换成多条数据及查询插图

1.2、数据处理，把逗号分割的由列转行

###特别注意：如果内容中包含符号&就报错这样导致报错XML 分析: 行 1，字符 39，需要分号
###这时候就要转换
##CROSS APPLY ( 
##	SELECT    CAST('<row>' + REPLACE(REPLACE(name, '&', ##'&amp;'),
##',','</row><row>')+ '</row>' AS XML) AS xmlcode
##) C1

 SELECT 
--列名
ROW_NUMBER() over(order by (select 1)) as row_id,
a.id,C1.*,item.c.value('.', 'varchar(50)') AS list
FROM #tempA a
CROSS APPLY ( 
	SELECT    CAST('<row>' + REPLACE(name, ',','</row><row>')+ '</row>' AS XML) AS xmlcode
) C1
CROSS APPLY xmlcode.nodes('*') item ( c );

基础技术、技术与框架SQLServer 以逗号分割的字符串转换成多条数据及查询插图1

案例二、查看逗号里的字符串是否存在

SELECT * FROM [news] t where charindex( ',' +'a'+',',',' +t.news_tag+',')>0

逗号拼接例子

SELECT STUFF((
    SELECT ',' + CAST(b.id AS VARCHAR(50))
    FROM #tempI a
    LEFT JOIN uf_db_AuthorityList b ON a.RoleCode = b.Code
    WHERE a.code = 'NC-CR-DH-M001'
    FOR XML PATH('')
), 1, 1, '') AS LongNames;

----实战例子
select '微信工作群' as SystemName,a.Name,a.FRemark,
(SELECT STUFF((
    SELECT ',' + CAST(a2.qcy AS VARCHAR(50))
    FROM uf_db_WechatChat_dt1 a2
    WHERE a2.mainid = a.id
    FOR XML PATH('')
), 1, 1, '')) AS LongNames
from uf_db_WechatChat	a
where a.FStatus=1 

----去重实战例子
select '微信工作群' as SystemName,a.Name,a.FRemark,
(SELECT STUFF((
    SELECT ',' + CAST(a2.qcy AS VARCHAR(50))
    FROM #TEMPB a2
    WHERE a2.mainid = a.id
		GROUP BY a2.qcy
    FOR XML PATH('')
), 1, 1, '')) AS LongNames
from #TEMPA	a
where a.FStatus=1 and a.id=1

打赏赞(1)