动态SQL pivot

Question

我使用以下SQL (使用SQL Server 2016)来透视来自我们的LMS (Moodle)的测试结果列表：

SELECT * 
FROM 
    (SELECT
         u.firstname AS name,
         u.lastname AS last_name,
         u.idnumber AS id_number,
         gi.itemname AS exam_name,
         CAST(gg.finalgrade / gi.grademax * 100 AS integer) AS grade
     FROM  
         mdl_grade_grades gg 
     INNER JOIN 
         mdl_grade_items gi ON gg.itemid = gi.id 
     INNER JOIN 
         mdl_course c ON gi.courseid = c.id 
     INNER JOIN 
         mdl_user u ON gg.userid = u.id
     WHERE 
         (gi.itemname IS NOT NULL) 
         AND (gi.courseid = 123)) SOURCE
PIVOT 
    (MAX(grade)
     FOR exam_name IN ([Exam ABC], [Exam DEF], [Exam GHI],
                       [Exam JKL], [Exam XYZ])
    ) PIVT
ORDER BY 
    id_number

结果将是：

name    last name   id number   division    region      branch      Exam ABC    Exam DEF    Exam GHI    Exam JKL    Exam XYZ
John    Tester      3343664     ABC         WEST RGN    A AGY       65                                  44
Kenny   Quipton     4342423     DDA         CENTRAL     RGN FRN     88          66          90                      89
Molefi  Manase      5456545     CCS         ABC RGN     XXX SOL     74          90          85          80          77

我唯一的问题是，考试名称必须硬编码在FOR IN(...)列表中，所以每次有变化时，列表都必须手动更新。

可以使用动态SQL重写此SQL吗？

哪个更好-使用Stuff()或FOR XML PATH，还是其他什么？

我不能使用存储过程(应用程序不支持它们)。

Answer 1

不需要存储过程和用于逗号连接的XML路径。只需将以下代码放入查询构建器并将其发送到数据库服务器即可。

DECLARE @SQL VARCHAR(MAX)='', @COL_NAMES VARCHAR(MAX)='';


--PREPARING EXAM NAMES LIST
SELECT @COL_NAMES = @COL_NAMES+ itemname+',' FROM (
SELECT DISTINCT  gi.itemname 
FROM mdl_grade_grades gg 
INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id 
INNER JOIN mdl_course c ON gi.courseid = c.id 
INNER JOIN mdl_course_categories cc ON c.category = cc.id 
INNER JOIN mdl_user u ON gg.userid = u.id
WHERE (gi.itemname IS NOT NULL) 
AND (gi.courseid = 123)
);

SELECT @COL_NAMES = LEFT(@COL_NAMES,LEN(@COL_NAMES)-1 );



--PREPARING DYNAMIC QUERY
SELECT @SQL = 
'SELECT * FROM (
    SELECT
        u.firstname AS name,
        u.lastname AS last_name,
        u.idnumber AS id_number,
        gi.itemname AS exam_name,
        CAST(gg.finalgrade / gi.grademax * 100 AS integer) AS grade
    FROM mdl_grade_grades gg 
    INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id 
    INNER JOIN mdl_course c ON gi.courseid = c.id 
    INNER JOIN mdl_course_categories cc ON c.category = cc.id 
    INNER JOIN mdl_user u ON gg.userid = u.id
    WHERE (gi.itemname IS NOT NULL) 
    AND (gi.courseid = 123)
) SOURCE
PIVOT (
    MAX(grade)
    FOR exam_name IN ('+@COL_NAMES+'
    )
) PIVT
ORDER BY id_number'


EXEC (@SQL);

Answer 2

这使用了for xml，但两者的工作方式应该是一样的

DECLARE @exams nvarchar(max), @sql nvarchar(max)
SET     @exams = SELECT STUFF(SELECT ',',  + QUOTENAME(t.itemname)
                         FROM (
                           SELECT DISTINCT gi.itemname 
                           FROM   mdl_grade_items gi 
                           WHERE  gi.itemname IS NOT NULL AND gi.courseid = 123
                           ORDER BY gi.itemname 
                         ) t
              FOR XML PATH(''), 0, 1, '')

SET    @sql = N'
    SELECT * 
    FROM 
       (SELECT
           u.firstname AS name,
           u.lastname AS last_name,
           u.idnumber AS id_number,
           gi.itemname AS exam_name,
           CAST(gg.finalgrade / gi.grademax * 100 AS integer) AS grade
        FROM  
           mdl_grade_grades gg 
        INNER JOIN 
           mdl_grade_items gi ON gg.itemid = gi.id 
        INNER JOIN 
           mdl_course c ON gi.courseid = c.id 
        INNER JOIN 
           mdl_user u ON gg.userid = u.id
        WHERE 
           (gi.itemname IS NOT NULL) 
           AND (gi.courseid = 123)) SOURCE
    PIVOT 
       (MAX(grade)
         FOR exam_name IN (' + @exams + ')
       ) PIVT
    ORDER BY 
       id_number 
'

EXEC sp_executesql @sql