假设有这样的表f1 f2 f3 f4
--------------------------------------------
a1 b1 1 2
a1 b2 3 4
转成这样的形式
a1 1 2 3 4
--------------------------------------------
a1 b1 1 2
a1 b2 3 4
转成这样的形式
a1 1 2 3 4
create table #
(f1 varchar(10) , f2 varchar(10), f3 int, f4 int)
insert into #
select 'a1', 'b1', 1 , 2 union all
select 'a1', 'b2', 3 , 4
declare @s varchar(100)
select @s=''
select @s=@s+','+cast(f3 as char(2)) from
(
select f1,f3 from #
union
select f1,f4 from #
)aprint stuff(@s,1,1,'')
/*1 ,2 ,3 ,4 */
create table #
(f1 varchar(10) , f2 varchar(10), f3 int, f4 int)
insert into #
select 'a1', 'b1', 1 , 2 union all
select 'a1', 'b2', 3 , 4
select * into T from
(
select f1,f3 from #
union
select f1,f4 from #
)acreate function aa(@name varchar(10))
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=''
select @s=@s+','+cast(f3 as char(5)) from t
return(stuff(@s,1,1,''))
endselect distinct f1,dbo.aa(f1)as f2 from t/*f1 f2
---------- ----------------------------------------------------------------------------------------------------
a1 1 ,2 ,3 ,4
*/
insert @t select 'a1','b1',1,2
insert @t select 'a1','b2',3,4 select f1,f2 =max(case when f2 = 'b1' then f3 else null end),
f3 =max(case when f2 = 'b1' then f4 else null end),
f4 =max(case when f2 = 'b2' then f3 else null end),
f5 =max(case when f2 = 'b2' then f4 else null end)from @t
group by f1
/*
f1 f2 f3 f4 f5
---- ----------- ----------- ----------- -----------
a1 1 2 3 4*/
insert into tb values('a1','b1',1,2)
insert into tb values('a1','b2',3,4)
goselect t1.f1,t1.f3,t1.f4 , t2.f3 f5 , t2.f4 f6 from
(select t.* from tb t where f2 = (select min(f2) from tb where f1 = t.f1)) t1,
(select t.* from tb t where f2 = (select max(f2) from tb where f1 = t.f1)) t2
where t1.f1 = t2.f1drop table tb
/*
f1 f3 f4 f5 f6
---------- ----------- ----------- ----------- -----------
a1 1 2 3 4(所影响的行数为 1 行)
*/
---------------------
a1 1 2 3 4