复杂的交叉表的查询方法

求一个复杂的交叉表的查询方法
有表
学生表stu
sid,sname
1,张三
2、李四
3、王五
有课程表class
cid,cname
1、语文
2、数学
3、英语
......
有成绩表score
id,sid,result
1,1,80

需要的结果
姓名，语文，数学、英语
张三，80，0，0
李四，0，0，0
王五，0，0，0

因实际情况比这复杂的多
要求：
1、以学生姓名（所有！）为第一列所有行的行头，以课程名称（所有！）为每一列的列标题
2、如果成绩表中有数据，则填充之,没有的话，则为0
3、学生数和课程数都假定不是固定不变的

说通俗点就是用学生表和课程表生成一个固定行名称和固定列名称的空表，再用成绩表的数据填充
在网上找了还多办法，都没办法做到第一条

只有100分了，还望高手相助

对了，我是要用在C#中


------解决思路----------------------

引用:

这个过程在SQL完成，如果数据量大了会很慢，为什么不在c#中完成呢？

我也是在C#里解决的！真去写SQL确实比较复杂
------解决思路----------------------

select * into #kc from

(

select 1 kid,'語文' kname union all

select 2,'數學' union all

select 3,'英語'

) a

select * into #stu from(

select 1 sid,'張三' sname union all

select 2,'李四' union all

select 3,'王五'

)b

select * into #score from

(

SELECT 1 kid,1 sid,80 cj

)c

declare @sql nvarchar(max),@col nvarchar(100),@col2 nvarchar(100)


select @col=ISNULL(@col+',','')+kname,@col2=ISNULL(@col2+',','')+'isnull('+kname+',0)'+kname from #kc


print @col

print @col2


set @sql='

select sname 姓名,'+@col2+' from 

(

	select c.kname,a.sname,b.cj from #stu a 

	left join #score b on a.sid=b.sid 

	left join #kc c on b.kid=c.kid

) p pivot(sum(cj) for kname in ('+@col+'))t order by sname desc'


print @sql

exec (@sql)


drop table #kc

drop table #score

drop table #stu

姓名   語文          數學          英語

---- ----------- ----------- -----------

張三   80          0           0

李四   0           0           0

王五   0           0           0

------解决思路----------------------
学生表和课程表交叉关联后初步生成（1）所要求的结果，在（1）的基础上作“行转列”后就可以生成最终结果。

;WITH stu([SID],sname) AS (

 

 SELECT 1,'张三' UNION ALL

 SELECT 2,'李四' UNION ALL

 SELECT 3,'王五'


), class(cid,cname) AS (


	SELECT 1,'语文' UNION ALL

	SELECT 2,'数学' UNION ALL

	SELECT 3,'英文'


 ), score(cid,[SID],result) AS (


	SELECT 1,1,80


 )

 , temp AS (

 SELECT A.SID,A.sname,B.cname,ISNULL(C.result,0) AS result

 FROM stu A 

 CROSS JOIN class B

 FULL JOIN score C ON A.SID = c.SID AND B.cid = C.cid

 )


SELECT sname AS 姓名,

	  MAX(CASE WHEN cname = '语文' THEN result ELSE 0 END) AS 语文,

	  MAX(CASE WHEN cname = '数学' THEN result ELSE 0 END) AS 数学,

	  MAX(CASE WHEN cname = '英文' THEN result ELSE 0 END) AS 英文

FROM temp

GROUP BY SID,sname

ORDER BY SID

------解决思路----------------------

这种列数不确定的，可以用动态语句来解决的，就是动态生成一个语句，其中关键的就是 动态生成了 实际所有的 课程列。
------解决思路----------------------
看下左连接，右连接你就懂了。
------解决思路----------------------

引用:

Quote: 引用:

这种列数不确定的，可以用动态语句来解决的，就是动态生成一个语句，其中关键的就是 动态生成了 实际所有的 课程列。

谢谢大家的回复
谢谢版主
动态的语句的话，我不会写，还有请大家帮助

我那个的话就是动态的
------解决思路----------------------

引用:

Quote: 引用:

这种列数不确定的，可以用动态语句来解决的，就是动态生成一个语句，其中关键的就是 动态生成了 实际所有的 课程列。

谢谢大家的回复
谢谢版主
动态的语句的话，我不会写，还有请大家帮助

可以参考一下这个：

在论坛中出现的比较难的sql问题：6(行转列问题1)
http://blog.****.net/sqlserverdiscovery/article/details/12949749