Rotate 3 rows into one row
I have a Quote and I want to get the last 3 notes for this quote in one line. Selecting the top 3 of the quotes, I have a QuoteNote table:
QuoteNoteID QuoteID Note NoteAreaID
1 1 Test 2
2 1 Test2 1
3 1 Test3 1
I have a selection in this table that returns the information I need
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
It gives me
QuoteNoteID QuoteID Note NoteAreaID NoteAreaDesc
1 1 Test 2 Internal
2 1 Test2 1 External
3 1 Test3 1 External
Now all I want to do is put this on one line
QuoteID Note1 Note2 Note3 NoteAreaID1 NoteAreaID2 NoteAreaID3 NoteAreaDesc1 NoteAreaDesc2 NoteAreaDesc3
1 Test Test2 Tes3 2 1 1 Internal External External
Any help would be appreciated, thanks
+3
source to share
1 answer
This should work for you:
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;
or
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;
Both should give the same results, but you can perform better than the others.
+2
source to share