日期:2014-05-18  浏览次数:20550 次

根据ID相同,将不同列显示在同一行
select ptp.ProjTaskID,
最后审核组长 = 
 case 
  when [IsLeaderReal] = 1 then p.PeopleName
 end,
组员=
 case 
  when [IsLeaderReal] = 0 then p.PeopleName
 end
 from ProjTaskPeople as ptp join #pTaskID as pTask on pTask.ProjTaskID = ptp.ProjTaskID 
join People as p on p.PeopleID = ptp.PeopleID order by pTask.ProjTaskID desc
查询结果:
ProjTaskID 最后审核组长 组员
zc55-303-C NULL 余娟
zc55-303-C 蔡国 NULL
zc53-301-C NULL 徐俊
zc53-301-C 邓凤 NULL
Z0402-303-A 李辉 NULL
U7115BF-302-U NULL 李新
U7115BF-302-U 杨欣 NULL
U7115BF-302-U NULL 邢东

怎么变成这样的结果:
ProjTaskID 最后审核组长 组员
zc55-303-C 蔡国 余娟
zc53-301-C 邓凤 徐俊
Z0402-303-A 李辉 NULL
U7115BF-302-U 杨欣 李新
U7115BF-302-U 杨欣 邢东
组长和组员是用 IsLeaderReal 来表示的,1为组长,0为组员,如果将组长,组员显示在同一行,我的sql 组长一行,组员一行

------解决方案--------------------
SQL code

--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([ProjTaskID] varchar(13),[最后审核组长] varchar(4),[组员] varchar(4))
insert [tbl]
select 'zc55-303-C',null,'余娟' union all
select 'zc55-303-C','蔡国',null union all
select 'zc53-301-C',null,'徐俊' union all
select 'zc53-301-C','邓凤',null union all
select 'Z0402-303-A','李辉',null union all
select 'U7115BF-302-U',null,'李新' union all
select 'U7115BF-302-U','杨欣',null union all
select 'U7115BF-302-U',null,'邢东'

select a.ProjTaskID,a.最后审核组长,b.组员 from(
select [ProjTaskID],[最后审核组长] from tbl where [最后审核组长] is not null)a
full join(select [ProjTaskID],[组员] from tbl 
where [组员] is not null)b  on a.ProjTaskID=b.ProjTaskID
/*
ProjTaskID    最后审核组长    组员
zc55-303-C    蔡国    余娟
zc53-301-C    邓凤    徐俊
Z0402-303-A    李辉    NULL
U7115BF-302-U    杨欣    李新
U7115BF-302-U    杨欣    邢东
*/

------解决方案--------------------
SQL code
select
    x.ProjTaskID,
    最后审核组长,
    组员
from 
(
select     
    a.ProjTaskID,p.PeopleName as 最后审核组长
from ProjTaskPeople as a
    inner join #pTaskID as b on a.ProjTaskID=b.ProjTaskID and a.[IsLeaderReal]=1
    inner join People as c on a.PeopleID=c.PeopleID 
)as x
inner join 
(
select     
    a.ProjTaskID,p.PeopleName as 组员
from ProjTaskPeople as a
    inner join #pTaskID as b on a.ProjTaskID=b.ProjTaskID and a.[IsLeaderReal]=0
    inner join People as c on a.PeopleID=c.PeopleID 
)    as y on x.ProjTaskID=y.ProjTaskID