关于Combine 2表，第二表行为列 [英] Regarding Combine 2 tables , 2nd table rows into columns

问题描述

嗨Frnds，



我有2张桌子

1）问题

2）QuestionOptions



表1：问题

 QuestId问题
  1  选择水果？ 
  2  选择动物？ 
  3  选择花？ 
 

表2：QuestionOptions

 OptionId 选项 QuestId CorrectOption 
  1  Apple  1   1  
  2 芒果 1   1  
  3  rose  1   0  
  4  Grapes  2   1  
  5  Lotus  2   0  
  6 橙色 3   0  
  7  rose  3   1  
  8  Cat  3   0  

我需要输出如下

< pre lang =SQL> QuestId Option1 Option2 Option3 CorrectOpt1 CorrectOpt2 CorrectOpt3

1 Apple Mango Rose 1 1 0
2 Grapes Lotus NULL 1 0 NULL
3 橙玫瑰猫 0 1 0

第一张表包含问题，第二张表包含答案。我尝试使用PIVOT概念但无法获得。



谢谢

Suresh

解决方案

  SELECT  
 QuestId 
，t1。[选项]  AS 选项1 
，t2。[选项]  AS  Option2 
，t3。[选项]  AS  Option3 
，t1.CorrectOption  AS  CorrectOpt1 
，t2.CorrectOption  AS  CorrectOpt2 
，t3.CorrectOption  AS  CorrectOpt3 
  FROM  
问题t 
  LEFT   OUTER   JOIN （
  SELECT  
 *，NUM = ROW_NUMBER（） OVER （ PARTITION   BY  QuestId  ORDER   BY  [选项]）
  FROM  
 QuestionOptions t 
）t1  ON  t1.QuestId = t.QuestId  AND  t1.NUM =  1  
  LEFT   OUTER   JOIN （
  SELECT  
 *，NUM = ROW_NUMBER（） OVER （ PARTITION   BY  QuestId  ORDER   BY  [选项]）
  FROM  
 QuestionOptions t 
）t2  ON  t2.QuestId = t.QuestId  AND  t2.NUM =  2  
  LEFT   OUTER   JOIN （
  SELECT  
 *，NUM = ROW_NUMBER（） OVER （ PARTITION   BY  QuestId  ORDER   BY  [ 选项]）
  FROM  
 QuestionOptions t 
）t3  ON  t3.QuestId = t.QuestId  AND  t3.NUM =  3  

作为Sergey Vaselenko解决方案1的替代方案，您可以加入两个支点：

  DECLARE   @ Questions   TABLE （QID  INT   IDENTITY （ 1 ， 1 ），问题 VARCHAR （ 300 ））
  INSERT   INTO   @ Questions （问题）
  VALUES （'  select fruits：'），
（' 选择动物：'），
（'' 选择鲜花：'）
 
 
  DECLARE   @ QuestionOptions   TABLE （QOID  INT   IDENTITY （ 1 ， 1 ），IOption  VARCHAR （ 300 ），QID  INT ， CorrectOption  INT ）
 
  INSERT   INTO   @ QuestionOptions （IOption，QID，CorrectOption）
  VALUES （'  Apple'， 1 ， 1 ），
（'  Mango'，  1 ， 1 ），
（'< /跨度>  rose'， 1 ， 0 ），
（'  Grapes'， 2 ， 1 ），
（'  Lotus '， 2 ， 0 ），
（' 橙色'， 3 ， 0 ），
（'  rose'， 3 ， 1 ），
（' < span class =code-string> Cat'， 3 ， 0 ）
 
  SELECT  T1.QID，T1.Question，T1。[ 1 ]  AS  Answer1，T1。[ 2 ]  AS  Answer2，T1。[  3 ]  AS  Answer3，T2。[ 1 ]  AS  IsProper1，T2。[ 2 ]  AS  IsProper2，T2。[ 3 ]  AS  IsProper3 
  FROM （
  SELECT  QID，问题，[ 1 ] ，[ 2 ]，[ 3 ] 
  FROM （
  SELECT  Q.QID，Q.Question，QO.IOption，ROW_NUMBER（） OVER （ PARTITION   BY  Q.QID 订购  BY  Q.QID） AS  OptNo 
  FROM   @ Questions   AS  Q  INNER  < span class =code-keyword> JOIN   @ QuestionOptions   AS  QO  ON  Q.QID = QO.QID 
） AS  DT 
 PIVOT（MAX（IOption）< span class =code-keyword> FOR  OptNo  IN （[ 1 ]，[  2 ]，[ 3 ]）） AS  PT 
） AS  T1  INNER   JOIN （
  SELECT 问题，[ 1 ]，[  2 ]，[ 3 ] 
  FROM （
  SELECT  Q.QID，Q.Question，QO.CorrectOption，ROW_NUMBER（） OVER （ PARTITION   BY  Q.QID  ORDER   BY  Q.QID） AS  OptNo 
  FROM   @ Questions   AS  Q  INNER   JOIN   @ QuestionOptions   AS  QO  ON  Q.QID = QO.QID 
） AS  ET 
 PIVOT（MAX （CorrectOption） FOR  OptNo  IN （[ 1 ]，[ 2  ]，[ 3 ]）） AS  RT 
） AS  T2  ON  T1.Question = T2.Question 

结果：

 1选择水果：Apple Mango rose 1 1 0 
 2选择动物：Grapes Lotus NULL 1 0 NULL 
 3选择鲜花：橙玫瑰猫0 1 0 

Hi Frnds,

I have 2 tables
1) Questions
2) QuestionOptions

Table 1: Questions

QuestId              Question
   1              select fruit?
   2              select animal?
   3              Select flower?

Table 2 : QuestionOptions

OptionId          Option     QuestId   CorrectOption
       1              Apple       1              1
       2              Mango       1              1
       3              rose        1              0 
       4              Grapes      2              1  
       5              Lotus       2              0
       6              Orange      3              0
       7              rose        3              1
       8              Cat         3              0 

I need output as follows

QuestId    Option1   Option2     Option3    CorrectOpt1 CorrectOpt2 CorrectOpt3 

   1        Apple     Mango        Rose        1             1         0
   2        Grapes    Lotus        NULL        1             0        NULL
   3        Orange    rose         Cat         0             1         0  

First Table Contains Questions and Second Table Contains Answers. I tried using PIVOT concept but not able to get.

Thanks
Suresh

解决方案

SELECT
    QuestId
    , t1.[Option] AS Option1
    , t2.[Option] AS Option2
    , t3.[Option] AS Option3
    , t1.CorrectOption AS CorrectOpt1
    , t2.CorrectOption AS CorrectOpt2
    , t3.CorrectOption AS CorrectOpt3
FROM
    Questions t
    LEFT OUTER JOIN (
        SELECT
            *, NUM = ROW_NUMBER() OVER(PARTITION BY QuestId ORDER BY [Option])
        FROM
            QuestionOptions t
    ) t1 ON t1.QuestId = t.QuestId AND t1.NUM = 1
    LEFT OUTER JOIN (
        SELECT
            *, NUM = ROW_NUMBER() OVER(PARTITION BY QuestId ORDER BY [Option])
        FROM
            QuestionOptions t
    ) t2 ON t2.QuestId = t.QuestId AND t2.NUM = 2
    LEFT OUTER JOIN (
        SELECT
            *, NUM = ROW_NUMBER() OVER(PARTITION BY QuestId ORDER BY [Option])
        FROM
            QuestionOptions t
    ) t3 ON t3.QuestId = t.QuestId AND t3.NUM = 3

Alternatively to solution1 by Sergey Vaselenko, you can join two pivots:

DECLARE @Questions TABLE (QID INT IDENTITY(1,1), Question VARCHAR(300))
INSERT INTO @Questions (Question)
VALUES('select fruits:'),
('select animals:'),
('Select flowers:')


DECLARE @QuestionOptions TABLE (QOID INT IDENTITY(1,1), IOption VARCHAR(300), QID INT, CorrectOption INT)

INSERT INTO @QuestionOptions (IOption, QID, CorrectOption)
VALUES('Apple', 1, 1),
('Mango', 1, 1),
('rose', 1, 0 ),
('Grapes', 2, 1  ),
('Lotus', 2, 0),
('Orange', 3, 0),
('rose', 3, 1),
('Cat', 3, 0 )

SELECT T1.QID, T1.Question, T1.[1] AS Answer1, T1.[2] AS Answer2, T1.[3] AS Answer3, T2.[1] AS IsProper1, T2.[2] AS IsProper2, T2.[3] AS IsProper3
FROM (
    SELECT QID, Question, [1], [2], [3]
    FROM (
        SELECT Q.QID, Q.Question, QO.IOption, ROW_NUMBER() OVER(PARTITION BY Q.QID ORDER BY Q.QID) AS OptNo
        FROM @Questions AS Q INNER JOIN @QuestionOptions AS QO ON Q.QID = QO.QID
        ) AS DT
        PIVOT(MAX(IOption) FOR OptNo IN ([1], [2], [3])) AS PT
    ) AS T1 INNER JOIN (
        SELECT Question, [1], [2], [3]
        FROM (
            SELECT Q.QID, Q.Question, QO.CorrectOption, ROW_NUMBER() OVER(PARTITION BY Q.QID ORDER BY Q.QID) AS OptNo
            FROM @Questions AS Q INNER JOIN @QuestionOptions AS QO ON Q.QID = QO.QID
            ) AS ET
            PIVOT(MAX(CorrectOption) FOR OptNo IN ([1], [2], [3])) AS RT
    ) AS T2 ON T1.Question = T2.Question

Result:

1   select fruits:  Apple   Mango   rose    1   1   0
2   select animals: Grapes  Lotus   NULL    1   0   NULL
3   Select flowers: Orange  rose    Cat     0   1   0

这篇关于关于Combine 2表，第二表行为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！