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

Why not just calculate everything you need in the dynamic pivot? Rather than using the quote restrictive `PIVOT` operator use conditional aggregation and you can do everything in one. — Thom A, Apr 08 '20 at 17:15
Hi - and thanks for your reply. Can you explain how that might work? If there were only three or four courses, yes, I could see using a CASE statement, but I've got perhaps 60 courses, and several hundred students, along with other variables such as YearOfStudy and CalendarYear to take into account, so I don't think that would work. Are you thinking of some other way that I've missed? Can you give me a code sample so that I can see what you're thinking? Thanks a lot — Andrew Richards, Apr 08 '20 at 18:04
Are you saying that your current dynamic `PIVOT` doesn't handle all that already? — Thom A, Apr 08 '20 at 18:05
Sorry - I'm clearly being dense. I don't know what you mean... Does my dynamic Pivot handle all what?! Clearly, it displays the figures below the appropriate headings, dynamically created, but it doesn't add the aggregate marks on the end, as I want. But I'd thought you were suggesting that I don't use PIVOT anyway because of it being restrictive... — Andrew Richards, Apr 08 '20 at 18:14

score 2 · Accepted Answer · answered Apr 08 '20 at 18:39

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.

Perfect - thanks so much for that. I hadn't realised that I could go on joining tables after the pivot. That's really helpful. -Thanks again. — Andrew Richards, Apr 09 '20 at 08:48

Safi Habhab · Answer 2 · 2020-04-08T18:43:43.987

your are writing sql for different tables, you want the results to be in one table. table joining is the way to do it.

query :

select TempStudentMarks.StudentID, avg(TempStudentMarks.mark) as 
average,max(TempStudentMarks.mark) as maximum,
Science.mark as science,French.Mark as french,Math.mark as math,
English.Mark as english from TempStudentMarks
LEFT JOIN 
    (
        select StudentID,mark  from TempStudentMarks where  CourseName='Science'
    ) AS Science
    on Science.StudentID=TempStudentMarks.StudentID
LEFT JOIN 
    (
        select StudentID,mark  from TempStudentMarks where  CourseName='French'
    ) AS French
    on French.StudentID=TempStudentMarks.StudentID

LEFT JOIN 
    (
        select StudentID,mark  from TempStudentMarks where  CourseName='Maths'
    ) AS Math
    on Math.StudentID=TempStudentMarks.StudentID

LEFT JOIN 
    (
        select StudentID,mark  from TempStudentMarks where  CourseName='English'
    ) AS English
    on English.StudentID=TempStudentMarks.StudentID

group by TempStudentMarks.StudentID,French.Mark,Science.Mark,English.Mark,Math.Mark

i didn't feel like writing a loop. you can give it a try by yourself if u want

Hi - thanks for your suggestion. I don't think this will work for me as there are too many courses, and the list of courses changes year by year, so this query would need to be re-written each year. I guess I could go down the road of using a cursor and looping that way, but I'd like to keep this as simple as possible, and a cursor seems like overhead that's better avoided. Thanks anyway. — Andrew Richards, Apr 09 '20 at 08:46

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

2 Answers2