原帖地址:http://topic.csdn.net/u/20090209/08/a945701c-e0d5-40cb-85f2-f4f56ac2999b.html
view plaincopy to clipboardprint?
--(1)字符串分拆并统计
create table #tb
(
id int,
col varchar(50),
num int
)
insert into #tb select 1,'aa,bb,cc',10
union all select 2,'aa,aa,bb',20
union all select 3,'aa,aa,bb',20
union all select 4,'dd,ccc,c',30
union all select 5,'ddaa,ccc',40
union all select 6,'eee,ee,c',50
declare @Len int
select top 1 @Len=len(col)+1 from #tb order by len(col)
select @Len
set rowcount @Len
select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B
set rowcount 0
;with hgo as
(
select b.id,
number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)
from #TT A join #tb b on substring(','+col,A.id,1)=','
)
select number,count(distinct id) [count],count(number) [number] from hgo group by number
--(2)分拆字符串求和
create table #tb
(
id int,
col varchar(50),
num int
)
insert into #tb select 1,'aa,bb,cc',10
union all select 2,'aa,aa,bb',20
union all select 3,'aa,aa,bb',20
union all select 4,'dd,ccc,c',30
union all select 5,'ddaa,ccc',40
union all select 6,'eee,ee,c',50
declare @len int
select top 1 @len=len(col)+1 from #tb order by len(col) desc
set rowcount @len
select id=identity(int,1,1) into # from dbo.syscolumns A,dbo.syscolumns B
set rowcount 0
;with hgo as
(
select distinct data=substring(A.col,b.id,charindex(',',A.col+',',b.id)-b.id),
A.num,b.id
from #tb A,# b
where substring(','+A.col,b.id,1)=','
)
select data,sum(num) from hgo group by data
--(3)合并法(函数)
create table tb1
(
col1 varchar(10),
col2 int
)
insert into tb1 select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3
create function dbo.FC_Str(@col1 varchar(100))
returns varchar(100)
as
begin
declare @i varchar(100)
set @i=''
select @i=@i+','+cast(col2 as varchar) from tb1 where col1=@col1
return(stuff(@i,1,1,''))
end
select col1,dbo.FC_Str(col1) from tb1 group by col1
--(4)固定行合并法
create table #tb
(
col1 varchar(10),
col2 int
)
insert into #tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'c',3
select col1,
col2=cast(min(col2) as varchar)+
case when count(*)=1 then ''
else +','+cast(max(col2) as varchar) end from #tb
group by col1
--(5)临时表合并法
if object_id('tb') is not null drop table tb
create table tb
(
col1 varchar(10),
col2 int
)
insert into tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3
select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2
declare @col1 varchar(20)
declare @col2 varchar(100)
update #t1 set @col2=
case when @col1=col1 then @col2+ ',' +col2
else col2 end,
@col1=col1,
col2=@col2
select * from #t1
select col1,col2=max(col2) from #t1 group by col1
--(6)字符串拆分
create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
returns @R table (Col nvarchar(100))
as
begin
declare @StrLen int
set @StrLen=len(@Str)
while charindex(@Split,@StrLen)>0
begin
insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
end
insert into @R values(@Str)
return
end
declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'
declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)
--(8)分拆数据到列
;declare @tb table
(
col varchar(20)
)
insert into @tb select 'aa,bb,cc'
union all select 'AAA,BB'
union all select 'AAA'
declare @i int,@j varchar
set @j=1
select col+',' as col into #t1 from @tb--加上','
select @i=max(len(Col)-len(replace(Col,',',''))) from #t1
while @j!>@i
begin
exec('alter table #t1 add [Col'+@j+'] varchar(10)')
exec('update #t1 set [Col'+@j+']=left(col,charindex('','',col)-1),col=stuff(col,1,charindex('','',col),'''') where Len(col)>1')
set @j=@j+1
end
alter table #T1 drop column Col
select * from #t1
drop table #t1;
/*
Col1 Col2 Col3
---------- ---------- ----------
aa bb cc
AAA BB NULL
AAA NULL NULL
*/
--(9)字符串分拆并统计
create table #tb
(
id int,
col varchar(50),
num int
)
insert into #tb select 1,'aa,bb,cc',10
union all select 2,'aa,aa,bb',20
union all select 3,'aa,aa,bb',20
union all select 4,'dd,ccc,c',30
union all select 5,'ddaa,ccc',40
union all select 6,'eee,ee,c',50
declare @Len int
select top 1 @Len=len(col)+1 from #tb order by len(col)
select @Len
set rowcount @Len
select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B
set rowcount 0
;with hgo as
(
select b.id,
number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)
from #TT A join #tb b on substring(','+col,A.id,1)=','
)
select number,count(distinct id) [count],count(number) [number] from hgo group by number
number count number
-------------------------------------------------- ----------- -----------
aa 3 5
bb 3 3
c 2 2
cc 1 1
ccc 2 2
dd 1 1
ddaa 1 1
ee 1 1
eee 1 1
(9 行受影响)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2010/01/22/5224723.aspx