-
多行合并2列并去掉重复列：
create table tb
(col1 varchar ( 10 ),
col2 varchar ( 10 ),
col3 varchar ( 10 ),
col4 varchar ( 10 ))
go
insert tb select ' 11111 ' , ' 222 ' , ' A ' , 1
insert tb select ' 1111 ' , ' 333 ' , ' A ' , 1
insert tb select ' 2222 ' , ' 999 ' , ' B ' , 2
insert tb select ' 3333 ' , ' 111 ' , ' B ' , 2
go
create FUNCTION dbo.f_str( @col3 varchar ( 10 ))
RETURNS varchar ( 8000 )
AS
BEGIN
declare @str varchar ( 8000 )
set @str = ''
select @str = @str +
case
when charindex ( ' , ' + col1 + ' - ' , ' , ' + @str + ' - ' ) = 0 then col1 + ' - '
else ''
end +
case
when charindex ( ' - ' + col2 + ' , ' , ' - ' + @str + ' , ' ) = 0 then col2 + ' , '
else ''
end
from tb
where col3 = @col3
return
left ( @str , len ( @str ) - 1 )
END
go
select distinct newcol = dbo.f_str(col3),col3,col4 from tb
drop table tb
drop function f_str
/*
newcol col3 col4
--------------------------------------
11111-222,1111-333 A 1
2222-999,3333-111 B 2
*/
create table tb
(col1 varchar ( 10 ),
col2 varchar ( 10 ),
col3 varchar ( 10 ),
col4 varchar ( 10 ))
go
insert tb select ' 11111 ' , ' 222 ' , ' A ' , 1
insert tb select ' 1111 ' , ' 333 ' , ' A ' , 1
insert tb select ' 2222 ' , ' 999 ' , ' B ' , 2
insert tb select ' 3333 ' , ' 111 ' , ' B ' , 2
go
create FUNCTION dbo.f_str( @col3 varchar ( 10 ))
RETURNS varchar ( 8000 )
AS
BEGIN
declare @str varchar ( 8000 )
set @str = ''
select @str = @str +
case
when charindex ( ' , ' + col1 + ' - ' , ' , ' + @str + ' - ' ) = 0 then col1 + ' - '
else ''
end +
case
when charindex ( ' - ' + col2 + ' , ' , ' - ' + @str + ' , ' ) = 0 then col2 + ' , '
else ''
end
from tb
where col3 = @col3
return
left ( @str , len ( @str ) - 1 )
END
go
select distinct newcol = dbo.f_str(col3),col3,col4 from tb
drop table tb
drop function f_str
/*
newcol col3 col4
--------------------------------------
11111-222,1111-333 A 1
2222-999,3333-111 B 2
*/
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638173.html，如需转载请自行联系原作者
