DECLARE @str VARCHAR(50), @sql VARCHAR(1000);SET @str='1,101,11,102,2,201,22,3,4,33';CREATE TABLE #T(id INT);SET @sql='SELECT k='+REPLACE(@str,',',' UNION ALL SELECT ');EXEC('INSERT #T SELECT * FROM('+@sql+') AS T')SET @str=''; SELECT @str=@str+','+RTRIM(id) FROM #T ORDER BY idSET @str=STUFF(@str,1,1,'');SELECT @str;DROP TABLE #T/* -------------------------------------------------- 1,2,3,4,11,22,33,101,102,201(1 行受影响) */
select id from tb order by cast(id as int)
Create table tb([Col1] int,[COl2] nvarchar(1000)) Insert tb select 1,N'1,101,11,102,2,201,22,3,4,33' union all select 2,N'2,9,4' union all select 3,N'1' Go --拆分 select a.COl1,Col2=cast(b.Col2 as int) into # from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from tb) a outer apply (select Col2=C.v.value('.','nvarchar(1000)') from a.COl2.nodes('/root/v')C(v)) b order by COl1,Col2 --合并 select col1,col2=stuff((select ','+rtrim(col2) from # where col1=a.col1 for xml path('')),1,1,'') from # a group by col1/* col1 col2 ----------- ---------------------------------- 1 1,2,3,4,11,22,33,101,102,201 2 2,4,9 3 1 */drop table # drop table tb
@str VARCHAR(50),
@sql VARCHAR(1000);SET @str='1,101,11,102,2,201,22,3,4,33';CREATE TABLE #T(id INT);SET @sql='SELECT k='+REPLACE(@str,',',' UNION ALL SELECT ');EXEC('INSERT #T SELECT * FROM('+@sql+') AS T')SET @str='';
SELECT @str=@str+','+RTRIM(id)
FROM #T
ORDER BY idSET @str=STUFF(@str,1,1,'');SELECT @str;DROP TABLE #T/*
--------------------------------------------------
1,2,3,4,11,22,33,101,102,201(1 行受影响)
*/
Insert tb
select 1,N'1,101,11,102,2,201,22,3,4,33' union all
select 2,N'2,9,4' union all
select 3,N'1'
Go
--拆分
select a.COl1,Col2=cast(b.Col2 as int) into #
from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from tb) a
outer apply (select Col2=C.v.value('.','nvarchar(1000)') from a.COl2.nodes('/root/v')C(v)) b
order by COl1,Col2
--合并
select col1,col2=stuff((select ','+rtrim(col2) from # where col1=a.col1 for xml path('')),1,1,'')
from # a
group by col1/*
col1 col2
----------- ----------------------------------
1 1,2,3,4,11,22,33,101,102,201
2 2,4,9
3 1
*/drop table #
drop table tb