首先，让我们定义表：

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

问题和评论/评论的数量直到运行时才知道。
我最终将为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