sql - 将 3 行转为一行

Question

我有一个报价单，我想将该报价单的最后 3 个注释放在一行中。通过从报价中选择前 3 个，我有一个 QuoteNote 表:

QuoteNoteID       QuoteID      Note        NoteAreaID
     1               1         Test            2
     2               1         Test2           1
     3               1         Test3           1

我在这张表上有一个选择，它带回了我需要的信息

SELECT q.QuoteNoteID, q.QuoteID, q.Note, q.NoteAreaID, n.Description AS NoteAreaDesc
FROM Quote q INNER JOIN QuoteNote n ON n.QuoteID = q.QuoteID

这给了我

QuoteNoteID       QuoteID      Note        NoteAreaID        NoteAreaDesc
     1               1         Test            2               Internal
     2               1         Test2           1               External
     3               1         Test3           1               External

现在我只想把它放在一行

QuoteID    Note1    Note2    Note3    NoteAreaID1     NoteAreaID2    NoteAreaID3    NoteAreaDesc1    NoteAreaDesc2      NoteAreaDesc3
  1         Test     Test2   Tes3         2                1              1           Internal            External       External           

任何帮助将不胜感激，谢谢

Answer 1

这应该适合你:

WITH QuoteNotes AS
(   SELECT  QuoteNote.QuoteNoteID, 
            Quote.QuoteID, 
            QuoteNote.Note, 
            QuoteNote.NoteAreaID, 
            [NoteAreaDesc] = QuoteNote.Description,
            [QuoteNoteNumber] = ROW_NUMBER() OVER(PARTITION BY Quote.QuoteID ORDER BY QuoteNote.QuoteNoteID)
    FROM    Quote
            INNER JOIN QuoteNote
                ON QuoteNote.QuoteID = Quote.QuoteID
)
SELECT  QuoteID,
        [Note1] = MAX(CASE WHEN QuoteNoteNumber = 1 THEN Note END),
        [Note2] = MAX(CASE WHEN QuoteNoteNumber = 2 THEN Note END),
        [Note3] = MAX(CASE WHEN QuoteNoteNumber = 3 THEN Note END),
        [NoteAreaID1] = MAX(CASE WHEN QuoteNoteNumber = 1 THEN NoteAreaID END),
        [NoteAreaID2] = MAX(CASE WHEN QuoteNoteNumber = 2 THEN NoteAreaID END),
        [NoteAreaID3] = MAX(CASE WHEN QuoteNoteNumber = 3 THEN NoteAreaID END),
        [NoteAreaDesc1] = MAX(CASE WHEN QuoteNoteNumber = 1 THEN NoteAreaDesc END),
        [NoteAreaDesc2] = MAX(CASE WHEN QuoteNoteNumber = 2 THEN NoteAreaDesc END),
        [NoteAreaDesc3] = MAX(CASE WHEN QuoteNoteNumber = 3 THEN NoteAreaDesc END)
FROM    QuoteNotes
GROUP BY QuoteID;

Example on SQL Fiddle

或者

WITH QuoteNotes AS
(   SELECT  QuoteNote.QuoteNoteID, 
            Quote.QuoteID, 
            QuoteNote.Note, 
            QuoteNote.NoteAreaID, 
            [NoteAreaDesc] = QuoteNote.Description,
            [QuoteNoteNumber] = ROW_NUMBER() OVER(PARTITION BY Quote.QuoteID ORDER BY QuoteNote.QuoteNoteID)
    FROM    Quote
            INNER JOIN QuoteNote
                ON QuoteNote.QuoteID = Quote.QuoteID
)
SELECT  q1.QuoteID,
        [Note1] = q1.Note,
        [Note2] = q2.Note,
        [Note3] = q3.Note,
        [NoteAreaID1] = q1.NoteAreaID,
        [NoteAreaID2] = q2.NoteAreaID,
        [NoteAreaID3] = q3.NoteAreaID,
        [NoteAreaDesc1] = q1.NoteAreaDesc,
        [NoteAreaDesc2] = q2.NoteAreaDesc,
        [NoteAreaDesc3] = q3.NoteAreaDesc
FROM    QuoteNotes q1
        LEFT JOIN QuoteNotes q2
            ON q1.QuoteID = q2.QuoteID
            AND q2.QuoteNoteNumber = 2
        LEFT JOIN QuoteNotes q3
            ON q1.QuoteID = q3.QuoteID
            AND q3.QuoteNoteNumber = 3 -- changed this to be 3
WHERE   q1.QuoteNoteNumber = 1;

两者应该产生相同的结果，但一个可能比另一个表现更好。

Example on SQL Fiddle