[MS SQL] 實作問卷調查結果交叉分析

問卷調查ER圖如下:

圖1: 簡易的問卷調查ERD

接下來根據此ERD，實作調查結果交叉分析表:

首先，請根據ERD，分別建立四個資料表(Table)：
Survey_Survey_Main、Survey_Subject、Survey_Answers、Survey_Result

--建立資料表 Survey_Survey_Main
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Survey_Survey_Main](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [SurveyName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Survey_Survey_Main] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--建立資料表 Survey_Subject
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Survey_Subject](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [MainID] [int] NOT NULL,
 [Subject] [nvarchar](50) NULL,
 CONSTRAINT [PK_Survey_Subject] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--建立資料表 Survey_Answers
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Survey_Answers](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [SubjectID] [int] NOT NULL,
 [AnswerName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Survey_Answers] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--建立資料表 Survey_Result
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Survey_Result](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [SubjectID] [int] NOT NULL,
 [AnswerID] [int] NOT NULL,
 [UserInfoID] [int] NOT NULL,
 CONSTRAINT [PK_Survey_Result] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

建立測試資料:

--開始建立測試資料...

--建立一個問卷
INSERT INTO Survey_Survey_Main SELECT '簡單的旅遊問卷'
DECLARE @MainID int;
SET @MainID = @@IDENTITY;
----------------------------------------------------------

--建立這個問卷的題目1
INSERT INTO Survey_Subject(MainID, [Subject]) 
SELECT @MainID, '下列地方你最想去哪裡玩?' 
DECLARE @SubjectID int;
SET @SubjectID = @@IDENTITY;

--建立題目1的答案(選項)
INSERT INTO Survey_Answers(SubjectID, AnswerName)
SELECT @SubjectID, '台北' UNION
SELECT @SubjectID, '台中' UNION
SELECT @SubjectID, '高雄' 
----------------------------------------------------------

--建立這個問卷的題目2
INSERT INTO Survey_Subject(MainID, [Subject]) 
SELECT @MainID, '你會搭什麼交通工具?'
SET @SubjectID = @@IDENTITY;

--建立題目2的答案(選項)
INSERT INTO Survey_Answers(SubjectID, AnswerName)
SELECT @SubjectID, '開車' UNION
SELECT @SubjectID, '火車' UNION
SELECT @SubjectID, '高鐵' UNION
SELECT @SubjectID, '飛機' 
----------------------------------------------------------

看一下結果:

再根據這個題目(Survey_Subject)及答案(Survey_Answers)資料表，產生Survey_Result資料表的資料，也就是使用者的填卷結果。

--以下產生三位填卷者的填卷結果
INSERT INTO Survey_Result(SubjectID, AnswerID, UserInfoID)
SELECT 1, 11, 1 UNION --台中
SELECT 2, 14, 1 UNION --火車
SELECT 1, 12, 2 UNION --台北
SELECT 2, 17, 2 UNION --開車
SELECT 1, 13, 3 UNION --高雄
SELECT 2, 15, 3    --飛機

看一下結果:

SELECT SR.UserInfoID '填卷者', SS.[Subject] '題目', SA.AnswerName '答案' 
FROM Survey_Result SR 
INNER JOIN Survey_Subject SS ON SR.SubjectID = SS.ID
INNER JOIN Survey_Answers SA ON SR.AnswerID = SA.ID
ORDER BY 1

測試資料都已建齊，接下來可以開始產生交叉分析表了。
在這裡我們要產生的是根據任意兩個題目間的交叉分析，因為測試資料只建了兩個題目，所以目前也只能分析這兩題，
你也可以自己建立更多的題目，然後選擇任意兩題來做交叉分析。

首先，你必須先產生題目與答案的所有交叉組合結果，並計算每個交叉組合的填答人數，
產生所有交叉組合結果的方法可以參考[MS SQL] 兩個資料表的所有交叉組合。
在這邊我建立一個資料表值函數來回傳這個結果:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[uf_Survey_RP001]
( 
 @SubjectID1 int, @SubjectID2 int
)
RETURNS TABLE 
AS
RETURN 
(
 SELECT TOP 100 PERCENT 
 AnswerID1,AnswerID2
 ,AnswerName1 , AnswerName2 
 ,(
  --計算人數
  SELECT ISNULL(COUNT(UserInfoID),0) FROM(
   SELECT UserInfoID FROM(
    SELECT UserInfoID, ISNULL(COUNT(UserInfoID),0) cnt
    FROM Survey_Result 
    WHERE (SubjectID = SubjectID1 AND AnswerID = AnswerID1)
    GROUP BY UserInfoID
    UNION ALL
    SELECT UserInfoID, ISNULL(COUNT(UserInfoID),0) cnt
    FROM Survey_Result 
    WHERE (SubjectID = SubjectID2 AND AnswerID = AnswerID2)
    GROUP BY UserInfoID
   ) a
  GROUP BY UserInfoID
  HAVING COUNT(UserInfoID) > 1
  ) b
 ) 'TotCnt'
 
 FROM 
 (
  SELECT SS.ID 'SubjectID1', SA.ID 'AnswerID1',SA.AnswerName 'AnswerName1'
  FROM Survey_Subject SS 
  INNER JOIN Survey_Answers SA ON SS.ID = SA.SubjectID 
  WHERE SS.ID = @SubjectID1
 )A, 
 ( 
  SELECT SS.ID 'SubjectID2', SA.ID 'AnswerID2',SA.AnswerName  'AnswerName2'
  FROM Survey_Subject SS 
  INNER JOIN Survey_Answers SA ON SS.ID = SA.SubjectID 
  WHERE SS.ID = @SubjectID2
 )B

 ORDER BY AnswerID1
)

看一下結果:

最後，建立一個預存程序(Stored Procedure)，輸入任兩題題目的ID，便可產生此兩題的交叉分析表。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================
-- Author:  
-- Create date: <2014/01/10>
-- Description: <問卷調查, 產生回答結果的交叉分析表>
-- EXEC dbo.[usp_Survey_RP001] 1,2
-- EXEC dbo.[usp_Survey_RP001] 2,1
-- ============================================================
ALTER PROCEDURE [dbo].[usp_Survey_RP001]
 @SubjectID1 int,@SubjectID2 int
AS
BEGIN

 SET NOCOUNT ON;
 DECLARE @SQL nvarchar(max), @strAnswerName nvarchar(max);
 SET @SQL = ''; 

 --產生橫軸的欄位字串
 SELECT @strAnswerName = COALESCE(@strAnswerName + ',', '') + QUOTENAME(AnswerName)
 FROM Survey_Subject SS INNER JOIN Survey_Answers SA
 ON SS.ID = SA.SubjectID 
 WHERE SS.ID = @SubjectID2
 ORDER BY SA.ID;

 --使用PIVOT 產生交叉分析表
 SET @SQL = @SQL + 'SELECT AnswerName1 , ' + @strAnswerName
 SET @SQL = @SQL + ' FROM ( '
 SET @SQL = @SQL + 'SELECT TotCnt,AnswerID1, AnswerName1, AnswerName2 '
 SET @SQL = @SQL + 'FROM dbo.uf_Survey_RP001('+CONVERT(nvarchar,@SubjectID1)+','+CONVERT(nvarchar,@SubjectID2)+') '
 SET @SQL = @SQL + ') p '
 SET @SQL = @SQL + 'PIVOT( '
 SET @SQL = @SQL + ' SUM(TotCnt) '
 SET @SQL = @SQL + ' FOR AnswerName2 IN (' + @strAnswerName + ') '
 SET @SQL = @SQL + ') AS pvt '
 SET @SQL = @SQL + 'ORDER BY pvt.AnswerID1 '
 
 EXEC(@SQL);
END

執行SP看看成果...

EXEC dbo.[usp_Survey_RP001] 1,2;

你可以試著把兩個題目的ID互換，看看會有什麼結果。
如果您有更好的做法，請務必Mail給我~ ^ ^