December 6, 2018 at 8:29 am
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 pvt
Is there a way to do this in one statement without writing dynamic sql?
Thanks for the help!
December 6, 2018 at 8:33 am
Uji2 - Thursday, December 6, 2018 8:29 AMIs there a way to do this in one statement without writing dynamic sql?
Thanks for the help!
Yes. It's actually pretty simple. See the following article...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2018 at 10:53 am
Thanks Jeff! That's exactly what I was looking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply