sql查询多个结果字段通过逗号分隔为同一行显示、sql查询结果有符号分隔的字段拆分多行显示

一、sql查询多个结果通过逗号分隔为同一行显示

sql查询数据结果

select e.ctrl_desc from t_ctrl_entry e inner join CodeGroupKeyCodes c on e.ctrl_code=c.KeyCode where c.GroupId='3060' and e.roomid=c.RoomId
 Group by e.ctrl_desc

 实现同一行逗号分隔显示

1、使用      FOR XML PATH ('')

这一部分已经可以实现列转行并用逗号分隔了，但是输出的是这样的格式：
，名字1，名字2，名字3

所以需要在外面再调用STUFF 的函数进行裁剪，把第一个逗号去掉，这样就可以了(下第二步)

select ','+t.ctrl_desc from (
 select e.ctrl_desc from t_ctrl_entry e inner join CodeGroupKeyCodes c on e.ctrl_code=c.KeyCode where c.GroupId='3060' and e.roomid=c.RoomId
 Group by e.ctrl_desc)t FOR XML PATH ('')

 2、使用STUFF 的函数进行裁剪第一个逗号

 select STUFF (sql语句 ,1,1, '' ) AS keycodename

 select STUFF (( 
 select ','+t.ctrl_desc from (
 select e.ctrl_desc from t_ctrl_entry e inner join CodeGroupKeyCodes c on e.ctrl_code=c.KeyCode where c.GroupId='3060' and e.roomid=c.RoomId
 Group by e.ctrl_desc)t FOR XML PATH ('')) ,1,1, '' ) AS keycodename

 

 二、sql查询结果有符号分隔的字段拆分多行显示

select RoomIdArr from [iControl4].[dbo].[CodeGroupRoom]

 拆分

select a.RoomIdArr
      ,SUBSTRING(a.RoomIdArr,number,CHARINDEX('|',a.RoomIdArr+'|',number)-number) as roomids
  from [iControl4].[dbo].[CodeGroupRoom] a  with(nolock) ,master..spt_values  with(nolock) 
  where 1=1
   and type='p'
   and SUBSTRING('|'+a.RoomIdArr,number,1)='|'