Creating a PIVOT with dynamic rows and columns – SQL Server 2016

First, let’s define the tables:

CREATE TABLE dbo.Reviews (
  id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  reviewer char(64) NOT NULL,
  reviewee char(64) NOT NULL,
  review_date datetime NOT NULL,
)

CREATE TABLE dbo.Questions (
  id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  question_name varchar(255) NOT NULL,
  question_description varchar(500) NOT NULL,
  input_type_id int NOT NULL,
  option_group_id INT NULL
)

CREATE TABLE dbo.Input_Types (
  id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  input_type_name varchar(80) NOT NULL
)

CREATE TABLE dbo.Option_Groups (
  id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  option_group_name varchar(45) NOT NULL
)

CREATE TABLE dbo.Option_Choices (
  id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  option_group_id int NOT NULL,
  option_choice_name varchar(45) NULL,
  option_choice_value int NULL
)

CREATE TABLE dbo.Answers (
  id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  review_id int NOT NULL,
  question_id int NULL,
  answer_numeric int NULL,
  answer_text varchar(max) NULL,
  answer_yn bit NULL
)

ALTER TABLE Answers WITH CHECK ADD FOREIGN KEY(question_id) REFERENCES Questions (id)
ALTER TABLE Answers WITH CHECK ADD FOREIGN KEY(review_id) REFERENCES Reviews (id)
ALTER TABLE Option_Choices WITH CHECK ADD FOREIGN KEY(option_group_id) REFERENCES Option_Groups (id)
ALTER TABLE Questions WITH CHECK ADD FOREIGN KEY(input_type_id) REFERENCES Input_Types (id)
ALTER TABLE Questions WITH CHECK ADD FOREIGN KEY(option_group_id) REFERENCES Option_Groups (id)

The following query gets all the answers with questions for a specific reviewee:

SELECT r.review_date, s1.q_id, s1.question_description, s1.answer_numeric 
  FROM Reviews r 
       LEFT JOIN (SELECT a.review_id, q.id q_id ,q.question_description, a.answer_numeric
                    FROM Answers a 
                         LEFT JOIN Questions q 
                         ON a.question_id=q.id
                    WHERE a.answer_numeric IS NOT NULL
                          AND a.question_id IS NOT NULL) s1
       ON r.id=s1.review_id
   WHERE r.reviewee = 'SOMEUNIQUEIDGOESHERE'

And the output looks more or less like this:

review_date              q_id question_description answer_numeric  
-----------------------------------------------------------------
2020-06-15 09:59:21.677  27   Role models blah     2
2020-06-15 09:59:21.677  29   Looks for blah       3
2020-06-15 09:59:21.677  30   Consistently blah    2
2020-06-26 13:58:58.420  27   Role models blah     5
2020-06-26 13:58:58.420  29   Looks for blah       4
2020-06-26 13:58:58.420  30   Consistently blah    4

What I want is output that looks like this:

q_id  question_description   2020-06-15  2020-06-26
---------------------------------------------------
27    Role models blah       2           5
29    Looks for blah         3           4
30    Consistently blah      2           4

The number of questions and reviews/review_dates is not known until runtime.

I am ultimately writing this query for a VB/ASP front end, so if it is easier to handle some of the dynamism through VB, that’s doable. It doesn’t have to be a purely SQL solution.

EDIT I tried the following query:

 DECLARE @cols AS VARCHAR(MAX), @query AS VARCHAR(MAX)
 SELECT @cols = STUFF((SELECT ',', QUOTENAME(review_date) FROM Reviews WHERE reviewee = '9AA1D3BAE1E0A9FA27B4857992548665C252931263B4D407CA8BBF21DC08D800' FOR XML PATH (''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
 SET @query = 'SELECT q_id, question_description, ' + @cols + ' FROM
  (SELECT r.review_date, s1.q_id, s1.question_description, s1.answer_numeric 
  FROM Reviews r 
       LEFT JOIN (SELECT a.review_id, q.id q_id ,q.question_description, a.answer_numeric
                    FROM Answers a 
                         LEFT JOIN Questions q 
                         ON a.question_id=q.id
                    WHERE a.answer_numeric IS NOT NULL
                          AND a.question_id IS NOT NULL) s1
       ON r.id=s1.review_id
 WHERE r.reviewee = ''UNIQUEIDGOESHERE''
 ) x 
 PIVOT
 (
   SUM(answer_numeric)
   FOR review_date IN (' + @cols + ')
   ) P
   ORDER BY q_id'
execute(@query);

It created the correct columns, but NULL results in the date columns

Answer

Okay, it turned out the last query was on the right track, the problem was the automatic date to string conversion implicitly happening from QUOTENAME was causing no matches in the IN clause.

Final correct query:

 DECLARE @cols AS VARCHAR(MAX), @query AS VARCHAR(MAX)
 SELECT @cols = STUFF((SELECT ',', QUOTENAME(FORMAT(review_date, 'dd-MMM-yyyy')) FROM Reviews WHERE reviewee = '9AA1D3BAE1E0A9FA27B4857992548665C252931263B4D407CA8BBF21DC08D800' FOR XML PATH (''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
 SET @query = 'SELECT q_id, question_description, ' + @cols + ' FROM
  (SELECT FORMAT(r.review_date, ''dd-MMM-yyyy'') rd, s1.q_id, s1.question_description, s1.answer_numeric 
  FROM Reviews r 
       LEFT JOIN (SELECT a.review_id, q.id q_id ,q.question_description, a.answer_numeric
                    FROM Answers a 
                         LEFT JOIN Questions q 
                         ON a.question_id=q.id
                    WHERE a.answer_numeric IS NOT NULL
                          AND a.question_id IS NOT NULL) s1
       ON r.id=s1.review_id
 WHERE r.reviewee = ''UNIQUEIDGOESHERE''
 ) x 
 PIVOT
 (
   SUM(answer_numeric)
   FOR rd IN (' + @cols + ')
   ) P
   ORDER BY q_id'
execute(@query);