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'
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