Pivot multiple columns into one row
-
I have a temp table that looks like this:
I need to pivot the values in TrackName, TrackStatus, and LastEvalDate into one row for each CrewMemberNotesID so that it looks like this:
Currently I'm doing this with 3 separate pivot statements - one for each column that I'm pivoting (TrackName, LastEvalDate, TrackStatus).
My code looks like this:
-- tracks
;WITH TrackName
AS
(
SELECT cs.CrewMemberNotesID,
cs.SortOrder,
cs.TrackName
FROM #tmp_crew_status cs
)
INSERT #tmp_crew_status_pivoted
(
CrewMemberNotesID,
Track1_Name,
Track2_Name,
Track3_Name,
Track4_Name,
Track5_Name,
Track6_Name,
Track7_Name,
Track8_Name
)
SELECT CrewMemberNotesID = CrewMemberNotesID,
Track1_Name = [1],
Track2_Name = [2],
Track3_Name = [3],
Track4_Name = [4],
Track5_Name = [5],
Track6_Name = [6],
Track7_Name = [7],
Track8_Name = [8]
FROM TrackName PIVOT (MAX(TrackName) FOR SortOrder IN ([1], [2], [3], [4], [5], [6], [7], [8])) AS pvtIs there a way to do this in one statement without writing dynamic sql?
Thanks for the help! - Uji2 - Thursday, December 6, 2018 8:29 AM
Yes. It's actually pretty simple. See the following article...
http://www.sqlservercentral.com/articles/T-SQL/63681/--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff! That's exactly what I was looking for.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply