Stored procedure that returns a table from 2 combined

Question

I am trying to write a stored procedure which returns a result combining 2 table variables which looks something like this.

Name | LastName | course | course | course  | course  <- Columns
Name | LastName | DVA123 | DVA222 | nothing | nothing <- Row1
Pete   Steven       200                               <- Row2    
Steve  Lastname              50                       <- Row3

From these 3 tables

Table Staff:

Name | LastName | SSN |
Steve  Lastname   234 
Pete   Steven     132

Table Course Instance:

Course | Year | Period |
DVA123   2013     1 
DVA222   2014     2

Table Attended by:

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

I am taking @year as a parameter that will decide what year in the course will be displayed in the result.

ALTER proc [dbo].[test4]
   @year int
as
begin

-- I then declare the 2 tables which I will then store the values from the tables
DECLARE @Table1 TABLE( 
   Firstname varchar(30) NOT NULL, 
   Lastname varchar(30) NOT NULL 
); 

DECLARE @Table2 TABLE( 
   Course varchar(30) NULL
);

Declare @variable varchar(max) -- variable for saving the cursor value and then set the course1 to 4

I want at highest 4 results/course instances which I later order by the period of the year

declare myCursor1 CURSOR
for SELECT top 4 period from Course instance
where year = @year

open myCursor1

fetch next from myCursor1 into @variable
--print @variable

while @@fetch_status = 0
    Begin

    UPDATE @Table2
    SET InstanceCourse1 = @variable
    where current of myCursor1

    fetch next from myCursor1 into @variable
    print @variable

    End

Close myCursor1
deallocate myCursor1

insert into @table1
select 'Firstname', 'Lastname'

insert into @table1
select Firstname, Lastname from staff order by Lastname

END

select * from @Table1 -- for testing purposes
select * from @Table2 -- for testing purposes
--Then i want to combine these tables into the output at the top

This is how far I've gotten, I don't know how to get the courses into the columns and then get the amount of hours for each staff member.

If anyone can help guide me in the right direction I would be very grateful. My idea about the cursor was to get the top (0-4) values from the top4 course periods during that year and then add them to the @table2.

Answer 1

Ok. This is not pretty. It is a really ugly dynamic sql, but in my testing it seems to be working. I have created an extra subquery to get the courses values as the first row and then Union with the rest of the result. The top four courses are gathered by using ROW_Number() and order by Year and period. I had to make different versions of the courses string I am creating in order to use them for both column names, and in my pivot. Give it a try. Hopefully it will work on your data as well.

DECLARE @Year INT
SET @Year = 2014
DECLARE @Query NVARCHAR(2000)

DECLARE @CoursesColumns NVARCHAR(2000) 
SET @CoursesColumns = (SELECT '''' + Course + ''' as c' + CAST(ROW_NUMBER() OVER(ORDER BY Year,     Period) AS nvarchar(50)) + ',' AS 'data()'
FROM AttendedBy where [Year] = @Year
for xml path(''))
SET @CoursesColumns = LEFT(@CoursesColumns, LEN(@CoursesColumns) -1)
SET @CoursesColumns = 
CASE 
    WHEN CHARINDEX('c1', @CoursesColumns) = 0 THEN @CoursesColumns + 'NULL as c1, NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c2', @CoursesColumns) = 0 THEN @CoursesColumns + ',NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c3', @CoursesColumns) = 0 THEN @CoursesColumns + ', NULL as c3, NULL as c4'
    WHEN CHARINDEX('c4', @CoursesColumns) = 0 THEN @CoursesColumns + ', NULL as c4'
    ELSE @CoursesColumns
END

DECLARE @Courses NVARCHAR(2000) 
SET @Courses = (SELECT Course + ' as c' + CAST(ROW_NUMBER() OVER(ORDER BY Year, Period) AS     nvarchar(50)) + ',' AS 'data()'
FROM AttendedBy where [Year] = @Year
for xml path(''))
SET @Courses = LEFT(@Courses, LEN(@Courses) -1)
SET @Courses = 
CASE 
    WHEN CHARINDEX('c1', @Courses) = 0 THEN @Courses + 'NULL as c1, NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c2', @Courses) = 0 THEN @Courses + ',NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c3', @Courses) = 0 THEN @Courses + ', NULL as c3, NULL as c4'
    WHEN CHARINDEX('c4', @Courses) = 0 THEN @Courses + ', NULL as c4'
    ELSE @Courses
END

DECLARE @CoursePivot NVARCHAR(2000) 
SET @CoursePivot = (SELECT  Course + ',' AS 'data()'
FROM AttendedBy where [Year] = @Year
for xml path(''))
SET @CoursePivot = LEFT(@CoursePivot, LEN(@CoursePivot) -1)

SET @Query = 'SELECT Name, LastName, c1, c2, c3, c4
FROM (
SELECT ''Name'' as name, ''LastName'' as lastname, ' +  @CoursesColumns +
' UNION

SELECT Name, LastName,' + @Courses +
' FROM(

SELECT 
    s.Name
    ,s.LastName
    ,ci.Course
    ,ci.Year
    ,ci.Period
    ,CAST(ab.Hours AS NVARCHAR(100)) AS Hours
FROM Staff s
LEFT JOIN AttendedBy ab
ON
s.SSN = ab.SSN
LEFT JOIN CourseInstance ci
ON
ab.Course = ci.Course
WHERE ci.Year=' + CAST(@Year AS nvarchar(4)) +
' ) q
PIVOT(
    MAX(Hours)
FOR 
    Course
IN (' + @CoursePivot + ')
)q2
)q3'


SELECT @Query
execute(@Query)

Edit: Added some where clauses so only courses from given year is shown. Added Screenshot of my results.