SSRS 中的调查报告

Question

我有两个表问题和人。人员参加调查后，将在人员调查表中输入人员和问题。

问题

问题 1 选项1 选项2 选项3 选项4

---

问题 2 选项1 选项2 选项3 选项4

人员调查表

人员1 问题1 选项2

---

人员1 问题2 选项3

---

人3 问题1 选项4

---

人员2 问题1 选项1

---

人员2 问题2 选项2

---

人员3 问题2 选项3

我需要在 SSRS 中创建一个矩阵报表，如下所示： 报告
问题1 选项1 选项2 选项3 选项4

---

1         1       0       1

---

问题 2 选项1 选项2 选项3 选项4

---

0        1        2       0

我的问题是：即使每个人只选择了某些选项，我如何获得每个问题的所有选项？

谢谢。

Answer 1

您需要创建一个查询，该查询将您质疑的所有选项联接在一起，然后将您的 PersonSurvey 表与该查询的结果左联接，并对 PersonSurvey 中的选项进行计数。

不幸的是，您的问题数据并非全部在一个字段中，因此需要取消透视。

这有点难以解释，所以这是我使用您的数据创建的示例。

SELECT 1 AS QUESTION_ID, '1Answer1' AS OPTION1, '1Answer2' AS OPTION2, '1Answer3' AS OPTION3, '1Answer4' AS OPTION4  
INTO #QUESTION 
UNION 
SELECT 2 AS QUESTION_ID, '2Answer1' AS OPTION1, '2Answer2' AS OPTION2, '2Answer3' AS OPTION3, '2Answer4' AS OPTION4  
UNION 
SELECT 3 AS QUESTION_ID, '3Answer1' AS OPTION1, '3Answer2' AS OPTION2, '3Answer3' AS OPTION3, '3Answer4' AS OPTION4  

SELECT 1 AS PERSON_ID, 1 AS QUESTION_ID, '1Answer2' AS ANSWER
INTO #PERSONSURVEY 
UNION 
SELECT 1 AS PERSON_ID, 2 AS QUESTION_ID, '2Answer3' AS ANSWER
UNION 
SELECT 2 AS PERSON_ID, 1 AS QUESTION_ID, '1Answer1' AS ANSWER
UNION 
SELECT 2 AS PERSON_ID, 2 AS QUESTION_ID, '2Answer2' AS ANSWER
UNION 
SELECT 3 AS PERSON_ID, 1 AS QUESTION_ID, '1Answer4' AS ANSWER
UNION 
SELECT 3 AS PERSON_ID, 2 AS QUESTION_ID, '2Answer3' AS ANSWER

;WITH QUESTIONS AS (
SELECT QUESTION_ID, OPTIONS, ANSWERS  
FROM #QUESTION 
UNPIVOT
(ANSWERS FOR OPTIONS IN (OPTION1, OPTION2, OPTION3, OPTION4 )) AS UNPVT
)
SELECT Q.QUESTION_ID, Q.OPTIONS, Q.ANSWERS, COUNT(P.ANSWER) AS ANSWERS 
FROM QUESTIONS Q 
LEFT JOIN #PERSONSURVEY P ON P.QUESTION_ID = Q.QUESTION_ID AND P.ANSWER = Q.ANSWERS
GROUP BY Q.QUESTION_ID, Q.OPTIONS, Q.ANSWERS 

生成的表与您给出的值匹配：

Q1- 1 1 0 1

Q2- 0 1 2 0

现在，您可以使用">问题"作为"行组"和"选项"作为">列分组"来创建矩阵。