继续提问 高分求动态sql

Question

Create table #jzScore (studentID int,ClassID int,SubjectID int,Score float,SchoolID int)insert into #jzScore values(1,1,1,90,1)
insert into #jzScore values(1,1,2,90,1)
insert into #jzScore values(1,1,3,90,1)insert into #jzScore values(2,1,1,90,1)
insert into #jzScore values(2,1,2,80,1)
insert into #jzScore values(2,1,3,70,1)insert into #jzScore values(3,2,1,90,1)
insert into #jzScore values(3,2,2,60,1)insert into #jzScore values(4,2,1,60,1)
insert into #jzScore values (4,2,2,80,1)
insert into #jzScore values (4,2,3,70,1)
得到如下结果
Studentid，1，2，3，总分，平均分，班级排名，年级排名
1       90  90  90  270  90        1       1
2       90 80 70  240    80         2       2
4       60 80 70  210   70          1       3
3     90 60 0      150  50          2       4
declare @sql varchar(8000)
set @sql='select StudentId,ClassId,schoolid'
select @sql= @sql+ ',max(case  when SubjectID='+ltrim(SubjectID)+' then Score else 0 end)'+quotename(SubjectID) 
from (select distinct subjectid from #jzscore) a
set @sql=@sql+',sum(score) as 总分,avg(score) as 平均分 into test' +' from #jzScore group by StudentId,ClassId,schoolid'
exec(@sql)

Answer 1

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: [地点]
IF OBJECT_ID('[地点]') IS NOT NULL DROP TABLE [地点]
CREATE TABLE [地点] (cd VARCHAR(2),name VARCHAR(4))
INSERT INTO [地点]
SELECT '01','北京' UNION ALL
SELECT '02','武汉' UNION ALL
SELECT '03','深圳'
 
--> 生成测试数据: [分店]
IF OBJECT_ID('[分店]') IS NOT NULL DROP TABLE [分店]
CREATE TABLE [分店] (cd VARCHAR(3),store VARCHAR(4))
INSERT INTO [分店]
SELECT '001','新科' UNION ALL
SELECT '002','新浪' UNION ALL
SELECT '003','小小'
 
--> 生成测试数据: [集合]
IF OBJECT_ID('[集合]') IS NOT NULL DROP TABLE [集合]
CREATE TABLE [集合] (cd INT,area_cd VARCHAR(2),company_cd VARCHAR(3))
INSERT INTO [集合]
SELECT 1,'01','001' UNION ALL
SELECT 2,'01','002' UNION ALL
SELECT 3,'03','001' UNION ALL
SELECT 4,'02','001'--SQL查询如下:DECLARE @SQL VARCHAR(8000);
SET @SQL=''SELECT
    @SQL=@SQL+',SUM(CASE WHEN B.area_cd='''+cd+''' THEN 1 ELSE 0 END) AS '+name
FROM [地点]EXEC('
    SELECT ISNULL(C.store,''合计'') AS store'+@SQL+',COUNT(b.company_cd) AS 总数
    FROM [地点] AS A
        LEFT JOIN [集合] AS B
            ON A.cd=B.area_cd
        RIGHT JOIN [分店] AS C
            ON B.company_cd=C.cd
    GROUP BY C.store
    WITH ROLLUP
    ORDER BY
        CASE WHEN store=''合计'' THEN 1 ELSE 0 END,
        总数
')/*
store 北京          武汉          深圳          总数
----- ----------- ----------- ----------- -----------
小小    0           0           0           0
新浪    1           0           0           1
新科    1           1           1           3
合计    2           1           1           4
(4 行受影响)
*/

Answer 2

declare @sql varchar(8000)
set @sql='select StudentId,ClassId,schoolid'
select @sql= @sql+ ',max(case  when SubjectID='+ltrim(SubjectID)+' then Score else 0 end)'+quotename(SubjectID) 
from (select distinct subjectid from #jzscore) a
set @sql=@sql+',sum(score) as 总分,avg(score) as 平均分 into test' +' from #jzScore group by StudentId,ClassId,schoolid'
exec(@sql)/*
store 北京          武汉          深圳          总数
----- ----------- ----------- ----------- -----------
小小    0           0           0           0
新浪    1           0           0           1
新科    1           1           1           3
合计    2           1           1           4
(4 行受影响)
*/

Answer 3

 思路是动态生成列，然后用exec()调用变量运行，
 楼上都对啊，学习