SQL语句行数据拆成多行和多行数据合并成一行的方法.docxVIP

SQL 语句一行拆成多行及多行合并成一行的方法 一、SQL 语句对一行（单元格）数据拆分成多行 有时候我们也许对一行数据拆分成多行的操作 例如： Col1 ? ? ? ?COl2 --------- ------------ 1 ? ? ? ? ? a,b,c 2 ? ? ? ? ? d,e 3 ? ? ? ? ? f 拆分成： Col1 ? ? ? ?COl2 -------- ----- 1 ? ? ? ? ? a 1 ? ? ? ? ? b 1 ? ? ? ? ? c 2 ? ? ? ? ? d 2 ? ? ? ? ? e 3 ? ? ? ? ? f 下面给出几个经常用到的方法： 1、?SQL2000用辅助表 if?object_id(Tempdb..#Num)?is?not?nulldrop?table?#Numgoselect?top?100?ID=Identity(int,1,1)?into?#Num?from?syscolumns?a,syscolumns?bSelect?a.Col1,COl2=substring(a.Col2,b.ID,charindex(,,a.Col2+,,b.ID)-b.ID)?from?Tab?a,#Num?bwhere??charindex(,,,+a.Col2,b.ID)=b.ID?--也可用?substring(,+a.COl2,b.ID,1)=, 2、SQL2005用Xml select?a.COl1,b.Col2from?(select?Col1,COl2=convert(xml,rootv+replace(COl2,,,/vv)+/v/root)?from?Tab)aouter?apply(select?Col2=C.v.value(.,nvarchar(100))?from?a.COl2.nodes(/root/v)C(v))b 3、用CTE with?roy?as?(?? select?Col1,COl2=cast(left(Col2,charindex(,,Col2+,)-1)?asnvarchar(100)), Split=cast(stuff(COl2+,,1,charindex(,,Col2+,),)?asnvarchar(100))?from?Tabunion?allselect?Col1,COl2=cast(left(Split,charindex(,,Split)-1)?as?nvarchar(100)),?? Split=cast(stuff(Split,1,charindex(,,Split),)?asnvarchar(100))?from?Roy?where?split )? select?COl1,COl2?from?roy?orderby?COl1?option?(MAXRECURSION?0) 二、SQL 语句SQL 多行数据合并为一个单元格（行） 描述：将如下形式的数据按id字段合并value字段。 id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果： id value ------ ----------- 1 aa,bb 2 aaa,bbb,ccc 即：group by id, 求 value 的和（字符串相加） */ --1、sql2000中只能用自定义的函数解决 create table tb(id int, value varchar(10)) insert into tb values(1, aa) insert into tb values(1, bb) insert into tb values(2, aaa) insert into tb values(2, bbb) insert into tb values(2, ccc) go create function dbo.f_str(@id varchar(10)) returns varchar(1000) as begin declare @str varchar(1000) select @str = isnull(@str + , , ) + cast(value as varchar) from tb where id = @id return @str end go --调用函数 select id , value = dbo.f_str(id) from tb group by id drop function dbo.f_str drop table tb --2、sql2