求一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
"状态为0的学生ID","状态为1的学生ID"
这两个字段其中一个为空或两个都为空

[rucypli]

case when

[pt1314917]

--> 测试数据: [ExemptStudent]
if object_id('[ExemptStudent]') is not null drop table [ExemptStudent]
create table [ExemptStudent] (ID varchar(36),Semester varchar(36),CourseCode datetime,Type int,ClassID varchar(36),StudentID varchar(36))
insert into [ExemptStudent]
select '9a78c2b7-bef2-44b8-a560-8169efbbe0eb','a6731776-0663-4155-9246-12906134b509','30031003',0,'d9bd5111-d444-47e6-82f5-01c449618a4a','ea182e26-0af7-4326-b1e5-2a341a93e1d2' union all
select '5c07ebb7-b6ee-4cea-8206-a6724186614e','a6731776-0663-4155-9246-12906134b509','30031001',0,'d9bd5111-d444-47e6-82f5-01c449618a4a','2310e6b3-605d-4cd7-8ca0-e4b50d430977' union all
select 'e5696bcb-a9c0-4b57-b807-aaf25c4a3c53','a6731776-0663-4155-9246-12906134b509','30031001',0,'d9bd5111-d444-47e6-82f5-01c449618a4a','eb05be12-56a5-4dba-883d-3c636b9f3e22' union all
select 'feebcef7-d19e-4b27-954c-ac91ad72ecc4','a6731776-0663-4155-9246-12906134b509','30031003',1,'d9bd5111-d444-47e6-82f5-01c449618a4a','2310e6b3-605d-4cd7-8ca0-e4b50d430977' union all
select '29574120-aed8-4852-9e3c-e3e577c8dbe3','a6731776-0663-4155-9246-12906134b509','30031003',0,'d9bd5111-d444-47e6-82f5-01c449618a4a','eb05be12-56a5-4dba-883d-3c636b9f3e22' union all
select '4b80ebdc-fa52-46ca-8445-fc3c3945faa7','a6731776-0663-4155-9246-12906134b509','30031001',1,'d9bd5111-d444-47e6-82f5-01c449618a4a','ea182e26-0af7-4326-b1e5-2a341a93e1d2'
go

create function get_studentid(@CourseCode varchar(40),@type int)
returns varchar(500)
as
begin
	declare @sql varchar(500)
	select @sql=isnull(@sql+',','')+ltrim(studentid) from ExemptStudent where CourseCode=@CourseCode and type=@type
	return @sql
end
go


select coursecode,[状态为0]=sum(case type when 0 then 1 else 0 end),
[状态为1]=sum(case type when 1 then 1 else 0 end),
[状态为0的学生ID]=dbo.get_studentid(coursecode,0),[状态为1的学生ID]=dbo.get_studentid(coursecode,1)
 from [ExemptStudent]
group by coursecode

--结果：
coursecode              状态为0        状态为1        状态为0的学生ID                                                                                                                                                                                                                                                        状态为1的学生ID
----------------------- ----------- ----------- --------------------------------------------------------------------------
3003-10-01 00:00:00.000 2           1           2310e6b3-605d-4cd7-8ca0-e4b50d430977,eb05be12-56a5-4dba-883d-3c636b9f3e22                                                                                                                                                                                        ea182e26-0af7-4326-b1e5-2a341a93e1d2
3003-10-03 00:00:00.000 2           1           ea182e26-0af7-4326-b1e5-2a341a93e1d2,eb05be12-56a5-4dba-883d-3c636b9f3e22                                                                                                                                                                                        2310e6b3-605d-4cd7-8ca0-e4b50d430977

[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

[ask_chang]

好吓人啊。


状态为0
状态为1
怎么计算得2或者1 ？
状态为0的学生ID
状态为1的学生ID

用什么条件？

都没有讲清楚，怎么做。