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 allselect 'zc55-303-C','蔡国',null union allselect 'zc53-301-C',null,'徐俊' union allselect 'zc53-301-C','邓凤',null union allselect 'Z0402-303-A','李辉',null union allselect 'U7115BF-302-U',null,'李新' union allselect 'U7115BF-302-U','杨欣',null union allselect 'U7115BF-302-U',null,'邢东'select a.ProjTaskID,a.最后审核组长,b.组员 from(select [ProjTaskID],[最后审核组长] from tbl where [最后审核组长] is not null)afull 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 李辉 NULLU7115BF-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 xinner 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