I doubt that you're actually using Table Variables for the real thing but we'll continue with that example. In the following, I've added indexes to the Table Variables in the form of PKs that I believe that you should have on your real tables. That will get rid of a pretty nasty sort that's showing up in Magoo's fine code.

You can also make the GROUP BY a whole lot easier and shorter if you understand that the IndexID for @Tbl is, in fact, unique and that allows you to use MAX for the StatusN columns to return the StatusN values without having to include them all in the GROUP BY. It's an ancient old-fart-that-hates-typing-and-reading-lots-of-code trick. 😛

Details are in the code...

--===== Declare the test tables

DECLARE @Tbl TABLE

(

IndexID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED --added PK

,Status1 INT

,Status2 INT

,Status3 INT

)

;

DECLARE @spTable TABLE

(

StatusID INT NOT NULL --added NOT NULL

,PriorityID INT NOT NULL --added NOT NULL

,PRIMARY KEY CLUSTERED(StatusID,PriorityID) --added PK

)

;

--===== Populate the test tables

INSERT @Tbl (Status1, Status2, Status3)

SELECT 1, 3, 1 UNION ALL --Converted to 2005 so others can play

SELECT 2, 1, 3 UNION ALL

SELECT 1, 3, 3

;

INSERT INTO @spTable

(StatusID, PriorityID)

SELECT 1, 20 UNION ALL --Converted to 2005 so others can play

SELECT 2, 15 UNION ALL

SELECT 3, 30

;

--===== Solve the problem like Magoo did but with the MAX trick to limit the GROUP BY.

-- I also extended the CASE statements to prevent the "NULL AGGREGATE" message.

SELECT t.IndexID

,Status1 = MAX(t.Status1)

,Priority1 = MAX(CASE WHEN t.Status1 = s.StatusId THEN s.PriorityID ELSE -1 END)

,Status2 = MAX(t.Status2)

,Priority2 = MAX(CASE WHEN t.Status2 = s.StatusId THEN s.PriorityID ELSE -1 END)

,Status3 = MAX(t.Status3)

,Priority3 = MAX(CASE WHEN t.Status3 = s.StatusId THEN s.PriorityID ELSE -1 END)

FROM @Tbl t

CROSS JOIN @spTable s

GROUP BY t.IndexID

ORDER BY t.IndexID

;

Of course, if your column names really are numeric in nature, you could do some pretty amazing and simple stuff to "auto-magically" generate all of the code instead of using CPR (Copy, Paste, Replace). 😉 It would also be a snap to change the column names in the output to Time-of-Day to make it easier for humans to read.

Magoo... hat's off to you, Sir. Awesome job with the CROSS JOINed CROSSTAB and recognizing the original table was the same as a preaggregation. It easily avoids an "unpivot, match, and repivot".