SQL Pivot without hardcoded values

Question

Using Microsoft SQL Server 2016 and a Moodle LMS database.

Is it possible to write this pivot code so that the 'Activity' names don't have to be hardcoded in the query?

SELECT * FROM (
    SELECT
        u.id,
        u.firstname AS 'Name',
        u.lastname AS 'Surname',
        u.idnumber AS 'ID Number',
        u.institution AS 'Division', 
        u.address AS 'Region',
        u.department AS 'Branch',
        ur.name AS 'Activity'
    FROM mdl_course_modules_completion cmc
    JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
    JOIN mdl_course c ON c.id = cm.course
    JOIN mdl_user u ON u.id = cmc.userid
    JOIN mdl_url ur ON ur.id = cm.instance
    WHERE cm.course IN (100,101,102)
    AND cm.module IN (23,9,14,26) -- 23 = url, 9 = label, 14 = scorm, 26 = assign
) SOURCE
PIVOT (
    COUNT(id)
    FOR Activity IN (
        [Activity ABC123],
        [Activity DEF234],
        [Activity GHI567],
        [Activity JKL890],
        [Activity MNO987],
        [Activity PQR657],
        [Activity STU814]
    )
) PIVT
ORDER BY Surname

I would prefer to get the 'Activity' names from this query instead of hardcoding them:

SELECT ur.name      
FROM mdl_url ur 
JOIN mdl_course_modules cm ON ur.id = cm.instance
WHERE cm.course IN (100,101,102)

Which gives me:

name
Activity ABC123
Activity DEF234
Activity GHI567
Activity JKL890
Activity MNO987
Activity PQR657
Activity STU814

I've tried this dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(ur.name) 
            FROM mdl_url ur 
            JOIN mdl_course_modules cm ON ur.id = cm.instance
            WHERE cm.course IN (100,101,102)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols

set @query = 'SELECT *, ' + @cols + ' FROM 
            (
    SELECT
        u.id,
        u.firstname AS Name,
        u.lastname AS Surname,
        u.idnumber AS ID_Number,
        u.institution AS Division, 
        u.address AS Region,
        u.department AS Branch,
        ur.name AS Activity
    FROM mdl_course_modules_completion cmc
    JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
    JOIN mdl_course c ON c.id = cm.course
    JOIN mdl_user u ON u.id = cmc.userid
    JOIN mdl_url ur ON ur.id = cm.instance
    WHERE cm.course IN (100,101,102)
    AND cm.module IN (23,9,14,26)
           ) x
            pivot 
            (
                COUNT(id)
                for Activity in (' + @cols + ')
            ) p '

execute(@query)

But it's giving me errors:

Msg 207, Level 16, State 1, Line 22
Invalid column name 'Value'.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'Point'.

So, my 2 questions are:

What am I doing wrong in the above code?
Is it possible to do the above without using dynamic sql?

Thanks

The easiest way to debug dynamic SQL is the `PRINT`/`SELECT` the SQL, debug that, then propagate the solution. If you do that, I suspect you'll very quickly have the solution. — Thom A, Jun 17 '21 at 12:57
Though, looking at the query *"Invalid column name 'Value'."* makes perfect sense. Your subquery aliased `x` has no column `value`. It also has no column `point`. — Thom A, Jun 17 '21 at 12:58
As far as I can tell, the SQL you have given us will now no longer produce those errors, @luisdev . If it is, you need to provide a [mre] if you've failed to debug it as I suggest in my original [comment](https://stackoverflow.com/questions/68019843/sql-pivot-without-hardcoded-values#comment120222811_68019843) — Thom A, Jun 17 '21 at 13:10
The problem now is that every single row contains a 0 value in each column where there should be a 1 value in some of the columns. — luisdev, Jun 17 '21 at 13:19
Then we need a [mre], along with expected results. *We* can't run your SQL so we don't know what the problem is. — Thom A, Jun 17 '21 at 13:25
I think I figured it out. Solution posted below. But can anybody see a better way to write this query? — luisdev, Jun 17 '21 at 13:39

luisdev · Answer 1 · 2021-06-17T13:46:20.673

I think I figured it out using this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(ur.name) 
            FROM mdl_url ur 
            JOIN mdl_course_modules cm ON ur.id = cm.instance
            WHERE cm.course IN (100,101,102)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *, ' + @cols + ' FROM 
            (
    SELECT
        u.id,
        u.firstname AS Name,
        u.lastname AS Surname,
        u.idnumber AS ID_Number,
        u.institution AS Division, 
        u.address AS Region,
        u.department AS Branch,
        ur.name AS Activity
    FROM mdl_course_modules_completion cmc
    JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
    JOIN mdl_course c ON c.id = cm.course
    JOIN mdl_user u ON u.id = cmc.userid
    JOIN mdl_url ur ON ur.id = cm.instance
    WHERE cm.course IN (100,101,102)
    AND cm.module IN (23,9,14,26)
           ) x
            pivot 
            (
                COUNT(id)
                for Activity in (' + @cols + ')
            ) p '

execute(@query)

Can anybody see a better way to do this? Is there a way to do this WITHOUT having to use dynamic sql?

*"But can anybody see a better way to do this? Is there a way to do this WITHOUT having to use dynamic sql?"* That's a question, not an answer. And yes, you can pivot without dynamic SQL, but the columns defined will be static. If you need dynamic columns, you need dynamic SQL. — Thom A, Jun 17 '21 at 13:41

SQL Pivot without hardcoded values

1 Answers1