CODEWITHSUNDEEP
sqlsql-serverstored-procedures
jlodenius
jlodenius

Reputation: 829

Stored procedure that returns a table with a matrix

I've got the following 3 tables.

Staff:

Name, LastName, SSN

Course Instance:

Course, Year, Period

Attended By:

Course, SSN, Year, Period, Hours

I'm trying to make a stored procedure that returns a table with a matrix over the first 4 course instances in a given year (taken as parameter to the procedure).

The returned matrix needs to look something like this:

Name | LastName | Course | Course | Course | Course

The four different Course are the first four courses from a given year, so what I've got now is this code to find those courses:

DECLARE myCursor CURSOR FOR
   SELECT top 4 Course 
   FROM Course Instance 
   WHERE Year = @year 
   ORDER by Period

The rest of the matrix should just get the values from the tables above somehow, basically every Staff member that has Attended any of the Courses should be in the matrix with correct information. And under each Course column I want the number of hours from the attending staff.

Sample tables

Staff: 

Name | LastName | SSN 
Steve  Lastname   234 
Pete   Steven     132

Course Instance: 

Course | Year | Period 
DVA123   2013   1 
DVA222   2014   2

Attended by: 

Course | SSN | Year | Period | Hours 
DVA123   234   2013   1        200 
DVA222   132   2014   2        50

Expected output from this:

Name | LastName | DVA123 | DVA222 | nothing | nothing
Pete   Steven     200
Steve  Lastname            50

Upvotes: 1

Views: 784

Answers (1)

GarethD
GarethD

Reputation: 69759

I am unsure of exactly how CourseInstance links to attended by, but you can achive this using PIVOT and ROW_NUMBER:

WITH Data AS
(   SELECT  s.Name, 
            s.LastName, 
            s.SSN,
            ci.Course,
            CourseNum = ROW_NUMBER() OVER(PARTITION BY s.SSN ORDER BY ci.Period)
    FROM    CourseInstance ci
            INNER JOIN AttendedBy a
                ON a.Course = ci.Course
                AND a.Year = ci.Year
                AND a.Period = ci.Period
            INNER JOIN Staff s
                ON s.SSN = a.SSN
    WHERE   ci.Year = @year 
)
SELECT  pvt.Name,
        pvt.LastName,
        Course1 = pvt.[1],
        Course2 = pvt.[2],
        Course3 = pvt.[3],
        Course4 = pvt.[4]
FROM    DATA
        PIVOT
        (   MAX(Course)
            FOR CourseNum IN ([1], [2], [3], [4])
        ) pvt;

It uses MAX(Course), but this is not really relevant since CourseNum is unique to each SSN you are only ever selecting the max of one row anyway. You could just as easily use MIN.

I do not advocate this approach one bit, but nevertheless, it will get you started should you chose the dynamic SQL approach instead of doing this in the presentation layer:

DECLARE @Courses NVARCHAR(MAX) = STUFF((SELECT  TOP 4 ',' + QUOTENAME(course)
                                        FROM    (   SELECT  course, Period, SortOrder = 0
                                                    FROM    CourseInstance
                                                    WHERE   Year = @Year
                                                    UNION ALL

                                                    -- THIS IS REQUIRED TO FILL GAPS WHERE THERE AREN'T ENOUGH COURSES
                                                    SELECT  TOP 4 
                                                            'Nothing' + CAST(ROW_NUMBER() OVER(ORDER BY object_id) AS CHAR(1)),
                                                            0,
                                                            1
                                                    FROM    sys.all_objects
                                                ) t
                                        ORDER BY SortOrder, Period, course
                                        FOR XML PATH(''), TYPE
                                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');


DECLARE @SQL NVARCHAR(MAX) = 
    '   SELECT  Name, LastName, ' + @Courses + '
        FROM    (   SELECT  s.Name, s.LastName, s.SSN, ci.Course, a.Hours
                    FROM    AttendedBy a
                            INNER JOIN Staff s
                                ON s.SSN = a.SSN
                            INNER JOIN CourseInstance ci
                                ON a.Course = ci.Course
                                AND a.Year = ci.Year
                                AND a.Period = ci.Period
                ) d
                PIVOT
                (   SUM(Hours)
                    FOR Course IN (' + @Courses + ')
                ) pvt;';

EXECUTE SP_EXECUTESQL @SQL;

Upvotes: 2

Related Questions