有没有想过这个TSQL挑战？

问题描述：

我有以下“调查申请”结果的物理表。这不是说存储结果的方式。有没有想过这个TSQL挑战？

正如你可以看到[84273x1x1]和[84273x1x2]是下拉列表问题，其中[84273x1x4]是一个自由文本，返回代码。这

UserID; UserName; Email; [84273x1x1]; [84273x1x2]; [84273x1x4]; [84273x2x5]; [84273x2x6]; [84273x2x7]; 
1; "Name1"; "[email protected]"; "A101", "A203", "Test answer bla bla"; "A102", "A201", "Test answer bla bla" 
2; "Name2"; "[email protected]"; "A102", "A202", "This is my comment"; "A101", "A203", "This is my comment";

的东西，我发现： [84273x1x1是对应于：

84273 = SurveyID 
1 = PageID 
1 = QuestionID

在回答表，它具有如下：

QID; Code; Answer; 
1; A101; 1 
1; A102; 2 
1; A103; 3 
2; A200; 0 
2; A201; 1 
2; A202; 2 
2; A203; 3 
5; A101; 1 
5; A102; 2 
5; A103; 3 
6; A200; 0 
6; A201; 1 
6; A202; 2 
6; A203; 3 

On the question table: 
QID; QuestionType; Title; 
1; "DropDownList"; "How do you rate of GROUP-Q1"; 
2; "DropDownList"; "How do you rate of GROUP-Q1"; 
3; "Text"; "Comment of Q1"; 
4; "DropDownList"; "How do you rate of GROUP-Q2"; 
5; "DropDownList"; "How do you rate of GROUP-Q2"; 
6; "Text"; "Comment of GROUP-Q2";

结果，我会喜欢实现的是枢轴转动：

UserID; Name; Email; Title; [Question1], [Question2]; [Question3] 
1; "Name1"; "[email protected]"; "GROUP-Q1"; "1"; "3"; "Test answer bla bla"; 
1; "Name1"; "[email protected]"; "GROUP-Q2"; "2"; "1"; "Test answer bla bla"; 
2; "Name2"; "[email protected]"; "GROUP-Q1"; "2"; "2"; "Test answer bla bla"; 
2; "Name2"; "[email protected]"; "GROUP-Q2"; "1"; "3"; "Test answer bla bla";

因为这个东西需要在TSQL-2005中完成。当我看到这个时，我的第一个想法就是它必须在Cursor中。

有没有想过家伙？

感谢

有多少个问题字段？这个数字是否有望增长？（我猜是的）。这种结构很难处理，最好的做法是将动态数据（问题列）转换为新表格，但我也理解这可能是不可能的。我不羡慕必须在这个结构上工作！ – JonVD 2010-11-10 23:40:26

答

如何像这样：

Select P.UserId, P.Username, P.Email 
    , 'GROUP-Q1' 
    , A1.Answer As Question1 
    , A2.Answer As Question2 
    , P.[84273x1x4] As Question3 
From People As P 
    Left Join Answers As A1 
     On A1.Code = P.[84273x1x1] 
      And A1.QID = 1 
    Left Join Answers As A2 
     On A2.Code = P.[84273x1x2] 
      And A2.QID = 2 
Union All 
Select P.UserId, P.Username, P.Email 
    , 'GROUP-Q2' 
    , A1.Answer As Question1 
    , A2.Answer As Question2 
    , P.[84273x1x7] As Question3 
From People As P 
    Left Join Answers As A1 
     On A1.Code = P.[84273x1x5] 
      And A1.QID = 5 
    Left Join Answers As A2 
     On A2.Code = P.[84273x1x6] 
      And A2.QID = 6

这里还有一个 “更” 动态的解决方案（需要SQL Server 2005 +）：

;With UserRawAnswers As 
    (
    Select UserId, 1 As QuestionID, [84273x1x1] As Answer From People 
    Union All Select UserId, 2, [84273x1x2] From People 
    Union All Select UserId, 3, [84273x1x4] From People 
    Union All Select UserId, 5, [84273x1x5] From People 
    Union All Select UserId, 6, [84273x1x6] From People 
    Union All Select UserId, 6, [84273x1x7] From People 
    ) 
    , UserAnswers As 
    (
    Select UA.UserId 
     , Right(Q.Title) As Title 
     , Coalesce(DropListAnswers.Answer, UA.Answer) As Answer 
    From UserRawAnswers As UA 
     Join Questions 
      On Questions.QID = UA.QID 
     Left Join (Answer As DropListAnswers 
      Join Questions As DropListQuestions 
       On DropListQuestions.QID = DropListAnswers.QID 
        And DropListQuestions.QuestionType = 'DropDownList') 
      On DropListAnswers.Code = UA.Answer 
    ) 
Select P.UserID, P.Name, P.Email 
    , UA.Title 
    , Min(Case When UA.QuestionID = 1 Then UA.Answer End) As Question1 
    , Min(Case When UA.QuestionID = 2 Then UA.Answer End) As Question2 
    , Min(Case When UA.QuestionID = 3 Then UA.Answer End) As Question3 
From UserAnswers As UA 
    Join People As P 
     On P.UserID = UA.UserId 
Group By P.UserID, P.Name, P.Email, UA.Title

请记住，固有的SQL语言不是用来处理动态模式（即动态生成的列）。构建动态模式的唯一方法是使用动态SQL，如果您达到这一点，则不妨在中间层或报告工具中执行此操作。而且，非规范化结构确实使分析困难。

有没有想过这个TSQL挑战？

相关推荐