Is it possible to combine columns from a view and a stored procedure?

Question

I'm using SQL Server 2017. I'm trying to return a set of data from SQL Server in a format which will ultimately go into an Excel spreadsheet. The data is complex, and although I could output the data in two sets into Excel, then use code to create a VLOOKUP to marry the datasets together, I'm trying to avoid that added complication.

To simplify slightly, I have a set of marks for students for courses they've taken. The table might look something like this:

CREATE TABLE TempStudentMarks 
(
    StudentID VARCHAR(4),
    CourseName VARCHAR(50),
    Mark INT
)

INSERT INTO TempStudentMarks (StudentID, CourseName, Mark)
VALUES ('1234', 'English', 78),
        ('1234', 'Maths', 68),
        ('1234', 'Science', 58),
        ('4321', 'English', 66),
        ('4321', 'Maths', 76),
        ('4321', 'French', 86),
        ('5555', 'Maths', 69),
        ('5555', 'Science', 49),
        ('5555', 'French', 69),
        ('6666', 'English', 33),
        ('6666', 'Maths', 44),
        ('6666', 'Science', 55),
        ('6666', 'French', 66)

I've created a stored procedure which outputs each student on a row, and each course in a column. It uses dynamic SQL because the number of courses varies by year and depending on a number of other factors:

CREATE PROCEDURE spTemp_StudentMarksPivot
AS
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @PivotCols NVARCHAR(MAX)

    SELECT 
        @PivotCols = COALESCE(@PivotCols + ',', '') + 
                     QUOTENAME(CourseName) 
    FROM 
        (SELECT DISTINCT CourseName 
         FROM TempStudentMarks) AS HeaderData

    PRINT @PivotCols

    SET @SQL = 'SELECT StudentID, ' + @PivotCols + ' 
                FROM (SELECT * FROM TempStudentMarks) SourceData
                PIVOT (MAX(Mark) FOR CourseName IN (' + @PivotCols + ')) AS PivotData'

    PRINT @SQL

    EXEC sys.sp_executesql @SQL

This works well enough; the output I get for this:

EXEC spTemp_StudentMarksPivot

looks like this:

StudentID English     French      Maths       Science
--------- ----------- ----------- ----------- -----------
1234      78          NULL        68          58
4321      66          86          76          NULL
5555      NULL        69          69          49
6666      33          66          44          55

I also have a view which does some statistical analysis. A simplified version looks like this:

CREATE VIEW vwStudentAggregates
AS
    SELECT tsm.StudentID, AVG(Mark) AS Average, MAX(Mark) AS BestMark
    FROM TempStudentMarks tsm
    GROUP BY tsm.StudentID

When I run a SELECT statement against that view, I get this:

StudentID Average     BestMark
--------- ----------- -----------
1234      68          78
4321      76          86
5555      62          69
6666      49          66

All of which is good.

But my question is about combining the two, to achieve an output like this:

StudentID English     French      Maths       Science     Average    BestMark
--------- ----------- ----------- ----------- ----------- ---------- ----------
1234      78          NULL        68          58          68         78
4321      66          86          76          NULL        76         76
5555      NULL        69          69          49          62         69
6666      33          66          44          55          49         66

Is this possible?

I can't see how I can create a temp table, given that I don't know the number or names of columns ahead of time. I've looked at the solution here:

Insert results of a stored procedure into a temporary table

And although I thought this might do the job, the situation is complicated by the fact that some of the users are logged in via Windows authentication whereas others must use SQL authentication, so whereas that solution uses OpenRowset, I fall over at the bit that says

SELECT * 
INTO #MyTempTable 
FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

because I don't know how to create a connection string that would work for everyone.

[Edit] I've just been playing around with that idea a little more, and get a whole host of errors, starting with

"Procedure sp_configure, Line 105 User does not have permission to perform this action."

and ending with

"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource"

so that's not looking good... (this work is for a client where the ICT dept are fairly tight on security, and not likely to want to grant additional permissions...

If anyone can help or guide, I'd be really grateful.

Many thanks Andrew

Answer 1

SET @SQL = '
SELECT PivotData.StudentID, ' + @PivotCols + ', vst.Average, vst.BestMark 
FROM (SELECT * FROM TempStudentMarks) SourceData
PIVOT (MAX(Mark) FOR CourseName IN (' + @PivotCols + ')) AS PivotData
join vwStudentAggregates as vst on PivotData.StudentID = vst.StudentID
';
--or
SET @SQL = '
select *
from
(
SELECT StudentID, ' + @PivotCols + '
FROM (SELECT * FROM TempStudentMarks) SourceData
PIVOT (MAX(Mark) FOR CourseName IN (' + @PivotCols + ')) AS PivotData
) as pd
join vwStudentAggregates as vst on pd.StudentID = vst.StudentID
';
--you can even parameterize the procedure, to return or not the studentaggregates and conditionally construct the executed @SQL.