如果只有p1列需要交叉:create table tb
(
IndexID int,
p1 varchar(20),
p2 varchar(20)
)
insert tb
select 1,null,'丙1-' union
select 3,'丁1',null union
select 1,'丙2-',null
goif exists(select 1 from sysobjects where id=object_id('f') and xtype='FN')
drop function f
go
create function f
(
@IndexID int
)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+p1 from tb where IndexID=@IndexID and p1 is not null
return stuff(@str,1,1,'')
end
go--测试
select IndexID,[p1]=dbo.f(IndexID),[p2]=isnull(max(p2),'')
from tb A
group by IndexID--删除测试环境
drop table tb
drop function f--结果
/*IndexID p1 p2
----------- ---------- ---------------
1 丙2- 丙1-
3 丁1 (所影响的行数为 2 行)
*/
(
IndexID int,
p1 varchar(20),
p2 varchar(20)
)
insert tb
select 1,null,'丙1-' union
select 3,'丁1',null union
select 1,'丙2-',null
goif exists(select 1 from sysobjects where id=object_id('f') and xtype='FN')
drop function f
go
create function f
(
@IndexID int
)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+p1 from tb where IndexID=@IndexID and p1 is not null
return stuff(@str,1,1,'')
end
go--测试
select IndexID,[p1]=dbo.f(IndexID),[p2]=isnull(max(p2),'')
from tb A
group by IndexID--删除测试环境
drop table tb
drop function f--结果
/*IndexID p1 p2
----------- ---------- ---------------
1 丙2- 丙1-
3 丁1 (所影响的行数为 2 行)
*/
select IndexID,min(P1) P1,min(P2) P2 from 表一
group by IndexID
(
IndexID int,
p1 varchar(20),
p2 varchar(20)
)
insert tb
select 1,null,'丙1-' union
select 3,'丁1',null union
select 1,'丙2-',null union
select 3,null,'戊3-'
goif exists(select 1 from sysobjects where id=object_id('getp1') and xtype='FN')
drop function getp1
go
create function getp1
(
@IndexID int
)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+p1 from tb where IndexID=@IndexID and p1 is not null
return isnull(stuff(@str,1,1,''),'')
end
go
if exists(select 1 from sysobjects where id=object_id('getp2') and xtype='FN')
drop function getp2
go
create function getp2
(
@IndexID int
)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+p2 from tb where IndexID=@IndexID and p2 is not null
return isnull(stuff(@str,1,1,''),'')
end
go--测试
select IndexID,[p1]=dbo.getp1(IndexID),[p2]=dbo.getp2(IndexID)
from tb A
group by IndexID--删除测试环境
drop table tb
drop function getp1,getp2--结果
/*IndexID p1 p2
----------- --------------------------------
1 丙2- 丙1-
3 丁1 戊3-(所影响的行数为 2 行)
*/
IndexID,
p1 = max(p1),
p2 = max(p2)
from
表一
group by
IndexID