0

Using SQL Server 2016 and referring to this article: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/

That article uses this pivot:

SELECT * FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Mathematics],
    [Science],
    [Geography]
  )
) AS PivotTable

How can you change the query so that the Subjects ([Mathematics], [Science], [Geography]) don't have to be hardcoded in the query?

Can you rather get the Subject list using a subquery? How do you get the FOR to work with a query like this?

  ...
  FOR [Subject]
  IN (
    SELECT subject FROM grades WHERE student = "Jacob"
  )
luisdev
  • 558
  • 7
  • 22
  • Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Jun 10 '21 at 07:56

1 Answers1

0

How can you change the query so that the Subjects ([Mathematics], [Science], [Geography]) don't have to be hardcoded in the query?

You can't; you'll have to form the SQL as a string and execute it dynamically

SQL makes it easy to have a variable number of columns (you just write more words in a SELECT), which then also makes it easy to forget that columns are like properties of an object (and an entire row is like an instance of an object); they aren't something that vary dynamically every time you run a program. As a Person you don't have a Name this week and not next week.

The number of columns output from a query isn't meant to vary; the number of rows is. If you want variable numbers of attributes, you'll have to form them as rows and then have your front end behave differently to account for them (i.e. don't do the pivot). If you can't do this because you have no front end, and you really do need a varying number of columns, you have to write a different SQL each time (which you can do by concatenating together a new SQL string and EXECing it, but be under no illusions - it works because it's a totally different SQL/the programmatic equivalent of you editing your hardcoded query and re-running it)

It looks something like (not tested - consider this pseudocode):

DECLARE @sql VARCHAR(4000) = CONCAT('
SELECT * FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (',

  SELECT STRING_AGG(Subject, ',') FROM (SELECT DISTINCT QUOTENAME(Subject) FROM Grades) x,

'  )
) AS PivotTable'
) --end concat

EXEC @sql
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Thanks. STRING_AGG() is a new feature in SQL Server 2017. As per my post, I'm using SQL Server 2016, so your solution throws a "'STRING_AGG' is not a recognized built-in function name" error. Can your solution be made to work in MSSQL 2016? – luisdev Jun 10 '21 at 09:05
  • It wasn't intended to be a "here, paste this and it will work", it was intended to highlight that you have to concat a CSV of column names together as a string, concat it into a SQL string and dynamically exec the string. You'll need to use one of the uglier methods of getting a CSV string from rows if string_agg is unavailable, like the fairly ubiquitous `STUFF(SELECT FOR XML(...)` approach which basically turns a query that returns rows into a block of xml then strips out the tags to create a csv string. – Caius Jard Jun 10 '21 at 09:21
  • I was in two minds whether to include any code at all, as I felt there was a risk it would cause readers to just skip over the TLDR blah blah words of reasoning, and go straight for the "giv me teh codez" at the end.. In summary: **you can't have dynamic columns without hacks, because you shouldn't do it; solve the problem another way**. Database queries that return variable numbers of columns *aren't very useful to other programs* – Caius Jard Jun 10 '21 at 09:23
  • Thanks Caius. Please explain your "you can't have dynamic columns without hacks, because you shouldn't do it;" comment further. – luisdev Jun 10 '21 at 15:28
  • Did you not read the entirety of my answer? – Caius Jard Jun 10 '21 at 16:14
  • Yes I did read your answer. And I asked for clarification. – luisdev Jun 10 '21 at 18:24
  • If you'd read the answer, you wouldn't have asked the question? The bold phrase in the comment is a summary of the answer? – Caius Jard Jun 10 '21 at 19:41