SQL Pivot with dynamic columns with SUM, COUNT or Aggregate results

Question

I have a query that generates results for learners on a specific course showing whether or not they have completed a module. Learners can take different modules from each other.

declare @CourseID int = 9   
SELECT LearnerID, UnitID, 
CASE WHEN (SUM(Total - [Total Achieved])) = 0 THEN 'Yes' ELSE 'No' END 
AS Completed FROM dbo.LMS_Achieved_Standards_Report 
GROUP BY CourseID, LearnerID, UnitID having CourseID = @CourseID

The results look like this

LearnerID  UnitID  Completed
15      15        Yes
15      28        No
28      28        Yes
116     150       Yes
79      12        No
69      34        Yes
69      15        No

I need it to look like this:

LearnerID     Unit 15  Unit 28  Unit 150  Unit 12  Unit 34
15              Yes      No
28                       Yes
116                               Yes
79                                          Yes
69              No                                   Yes

The other factor as already stated is that they can all take different units so I can't create a PIVOT with set column headings.

I have looked at other examples of PIVOT with dynamic columns, tried a whole bunch of examples and just cannot work this out.

I would really appreciate some help?

Thanks

Can you show one of the things you tried that didn't work out? How can we help you understand what you did wrong if we don't see it? You're not going to learn how to do it if you just copy an answer we give. — Barmar, Apr 29 '14 at 21:33
do you know if you have execute permissions on the database you are trying to run dynamic sql on? if not, dynamic sql will not work for you. also what database are you using? what have you tried? — rhealitycheck, Apr 29 '14 at 21:38
Hi Barmar - I would need to redo them. I got so frustrated I deleted them all. Sorry - next time I will add them — gillers322, Apr 29 '14 at 21:42
@gillers322 - have you looked at this example and tried any of the code in there (and can you post any errors you get if you do try): http://stackoverflow.com/questions/21197547/sum-each-column-for-a-table-with-over-20-columns-without-writing-out-each-sum — rhealitycheck, Apr 29 '14 at 21:44

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

This dynamic pivot approach works well in these situations:

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(UnitID) 
            FROM TheReport
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT LearnerID, ' + @cols + ' from 
            TheReport
            pivot 
            (
                min(Completed)
                for UnitId in (' + @cols + ')
            ) p '
 execute(@query);

TheReport is the result of your query - you can include it verbatim as a derived table.

SqlFiddle here

One thing still left for you is to do the column aliasing (currently left as the original Unit IDs) - another derived table step will allow you to do this.

edited May 23 '17 at 12:28

Community

1
1

answered Apr 29 '14 at 21:43

StuartLC

104,537
17
209
285

I am getting an invalid column name 'Completed' - is this because it is created by a CASE statement? – gillers322 Apr 29 '14 at 21:47
Glad to hear this. It might make sense to dump the result of your query into a temp table and use this as `#TheReport` otherwise you'll be evaluating the query twice - once to get the columns, and the again to do the pivot. – StuartLC Apr 29 '14 at 22:01

SQL Pivot with dynamic columns with SUM, COUNT or Aggregate results

1 Answers1