关于Combine 2表，第二表行为列

本文介绍了关于Combine 2表，第二表行为列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 嗨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 我需要输出如下 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 ），（' 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 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 问题，[ 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) QuestionOptionsTable 1: QuestionsQuestId 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 0I need output as followsQuestId 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 0First Table Contains Questions and Second Table Contains Answers. I tried using PIVOT concept but not able to get.ThanksSuresh 解决方案 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 CorrectOpt3FROM 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 = 3Alternatively 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 IsProper3FROM ( 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.QuestionResult:1 select fruits: Apple Mango rose 1 1 02 select animals: Grapes Lotus NULL 1 0 NULL3 Select flowers: Orange rose Cat 0 1 0 这篇关于关于Combine 2表，第二表行为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！