求一SQL语句

[shixixi1987]

表名ExemptStudent
字段有ID,学期Semester,课程代码CourseCode,状态Type,班级ID ClassID,学生ID StudentID
9a78c2b7-bef2-44b8-a560-8169efbbe0eb a6731776-0663-4155-9246-12906134b509 30031003 0 d9bd5111-d444-47e6-82f5-01c449618a4a ea182e26-0af7-4326-b1e5-2a341a93e1d2

5c07ebb7-b6ee-4cea-8206-a6724186614e a6731776-0663-4155-9246-12906134b509 30031001 0 d9bd5111-d444-47e6-82f5-01c449618a4a 2310e6b3-605d-4cd7-8ca0-e4b50d430977

e5696bcb-a9c0-4b57-b807-aaf25c4a3c53 a6731776-0663-4155-9246-12906134b509 30031001 0 d9bd5111-d444-47e6-82f5-01c449618a4a eb05be12-56a5-4dba-883d-3c636b9f3e22

feebcef7-d19e-4b27-954c-ac91ad72ecc4 a6731776-0663-4155-9246-12906134b509 30031003 1 d9bd5111-d444-47e6-82f5-01c449618a4a 2310e6b3-605d-4cd7-8ca0-e4b50d430977

29574120-aed8-4852-9e3c-e3e577c8dbe3 a6731776-0663-4155-9246-12906134b509 30031003 0 d9bd5111-d444-47e6-82f5-01c449618a4a eb05be12-56a5-4dba-883d-3c636b9f3e22

4b80ebdc-fa52-46ca-8445-fc3c3945faa7 a6731776-0663-4155-9246-12906134b509 30031001 1 d9bd5111-d444-47e6-82f5-01c449618a4a ea182e26-0af7-4326-b1e5-2a341a93e1d2

我想实现效果如下
同一班级ID下
课程代码 状态为0 状态为1 状态为0的学生ID 状态为1的学生ID
30031001 2 1 2310E6B3-605D-4CD7-8CA0-E4B50D430977,EB05BE12-56A5-4DBA-883D-3C636B9F3E22 EA182E26-0AF7-4326-B1E5-2A341A93E1D2
30031003 2 1 EA182E26-0AF7-4326-B1E5-2A341A93E1D2,EB05BE12-56A5-4DBA-883D-3C636B9F3E22 2310E6B3-605D-4CD7-8CA0-E4B50D430977

[nightmaple]

select ClassID,CourseCode,sum(case when Type=0 then 1 else 0 end) as Type0,
	sum(case when Type=1 then 1 else 0 end) as Type1,
	case when Type=0 then StudentID else null end as StudentID0,
	case when Type=1 then StudentID else null end as StudentID1
from ExemptStudent
group by ClassID,CourseCode,case when Type=0 then StudentID else null end,
	case when Type=1 then StudentID else null end

[shixixi1987]

如果状态为0的学生ID有两条以上就用','隔开显示在一个字段里面
如果状态为1的学生ID有两条以上就用','隔开显示在一个字段里面

[永生天地]

create function f_testS(@CourseCode varchar(20),@Type int)
returns varchar(2000)
as
begin
	declare @s varchar(2000)
	set @s=''
	select @s=@s+','+StudentID
	from ExemptStudent 
	where CourseCode=@CourseCode
	and type=@type
	set @s=stuff(@s,1,1,'')
	return @s
end


select CourseCode 课程代码,
sum(case when Type=0 then 1 else 0 end)状态为0,
sum(case when Type=1 then 1 else 0 end)状态为1,
dbo.f_testS(CourseCode,0 )状态为0的学生ID,
dbo.f_testS(CourseCode,1 )状态为1的学生ID
from ExemptStudent
group by CourseCode

/*
课程代码                 状态为0        状态为1        状态为0的学生ID                                                    状态为1的学生ID                                                    
-------------------- ----------- ----------- ------------------------------------------------------------ ------------------------------------------------------------ 
30031001             2           1           2310e6b3-605d-4cd7-8ca0-e4b50d430977,eb05be12-56a5-4dba-883d ea182e26-0af7-4326-b1e5-2a341a93e1d2
30031003             2           1           ea182e26-0af7-4326-b1e5-2a341a93e1d2,eb05be12-56a5-4dba-883d 2310e6b3-605d-4cd7-8ca0-e4b50d430977

（所影响的行数为 2 行）

*/

[nightmaple]

貌似楼上的方法简单点~~~不过我的方法也可行哈~~~

select ClassID,CourseCode,sum(case when Type=0 then 1 else 0 end) as Type0,
	sum(case when Type=1 then 1 else 0 end) as Type1,
	case when Type=0 then StudentID else null end as StudentID0,
	case when Type=1 then StudentID else null end as StudentID1
into #temp from ExemptStudent
group by ClassID,CourseCode,case when Type=0 then StudentID else null end,
	case when Type=1 then StudentID else null end

declare @classid varchar(20),@course varchar(20),@student0 varchar(200),@student1 varchar(200)
select @classid='',@course='',@student0='',@student1=''

update #temp set @student0=StudentID0=case when @classid=ClassID and @course=CourseCode then 
	@student0+','+StudentID0 else StudentID0 end,
	@student1=StudentID1=case when @classid=ClassID and @course=CourseCode then 
	@student1+','+StudentID1 else StudentID1 end,
	@classid=ClassID,@course=CourseCode

select ClassID,CourseCode,Type0,Type1,MAX(StudentID0),MAX(StudentID0) from #temp group by ClassID,CourseCode,Type0,Type1

[veritasfx]

哇，代码很漂亮。
虽然看起来脑子有点晕。

[zhengtw]

select 课程代码 = CourseCode
	, 状态0 = (select count(Type) from ExemptStudent where CourseCode = e.CourseCode and Type = 0)
	, 状态1 = (select count(Type) from ExemptStudent where CourseCode = e.CourseCode and Type = 1)
	, 状态为0的学生ID = stuff((select ',' + StudentID from ExemptStudent where CourseCode = e.CourseCode and Type = 0 for xml path('')),1,1,'')
	, 状态为1的学生ID = stuff((select ',' + StudentID from ExemptStudent where CourseCode = e.CourseCode and Type = 1 for xml path('')),1,1,'')
from ExemptStudent e
group by CourseCode

[shixixi1987]

if exists(select * from sysobjects where [name]='A')
drop table A
create table A
(
ID int,
[Desc] varchar(10)
)
go
insert into A(ID,[Desc])
(
select 1,'test1' union
select 2,'test2'
)
go
if exists(select * from sysobjects where [name]='B')
drop table B
create table B
(
AID int,
SEQ int,
[Desc] varchar(10)
)
go
insert into B(AID,SEQ,[Desc])
(
select 1,1,'B1' union
select 1,2,'B2' union
select 1,3,'B3' union
select 2,1,'B4' union
select 2,2,'B5'
)
go
select ID =BB.ID,[Desc]
, Desc1 = stuff((select ',' + Convert(varchar(10),Desc1)
from (select A.ID,A.[Desc],B.[Desc] Desc1 from A,B where A.ID=B.AID) AA
where AA.ID =BB.ID for xml path('')),1,1,'')
from (select A.ID,A.[Desc],B.[Desc] Desc1 from A,B where A.ID=B.AID) BB
group by ID,[Desc]