创建具有动态行和列的透视 - SQL Server 2016

Question

首先，让我们定义表：

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)

以下查询获取特定审阅者的所有问题的答案和问题：

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'

输出看起来或多或少是这样的：

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

我想要的是如下所示的输出：

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

问题和评论/review_dates的数量直到运行时才知道。

我最终是为 VB/ASP 前端编写此查询的，因此，如果通过 VB 更容易处理一些动态，那是可行的。 它不一定是纯粹的SQL解决方案。

编辑我尝试了以下查询：

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);

它创建了正确的列，但 NULL 导致日期列

Answer 1

好的，事实证明最后一个查询是正确的，问题是从 QUOTENAME 隐式发生的字符串转换的自动日期导致 IN 子句中没有匹配项。

最终正确查询：

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);