Table join / pivoting question

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".

Yes, my table is quite large and it does have primary keys, and I do generate the code automatically. In any case, here's a version that I ended up using which uses cross apply, instead of the case statement:

selectX.IndexID,

X.Status1,

Priority1 = case when PX.ColID = 1 then S.PriorityID end,

X.Status2,

Priority2 = case when PX.ColID = 2 then S.PriorityID end,

X.Status3,

Priority3 = case when PX.ColID = 3 then S.PriorityID end

from@tbl X cross apply

(

values( 1, X.Status1 ),

( 2, X.Status2 ),

( 3, X.Status3 )

) PX ( ColID, StatusID ) inner join

@spTable S on

S.StatusID = PX.StatusID

group by X.IndexID, X.Status1, X.Status2, X.Status3

First, let me say thanks Jeff (mostly for not saying "You've done it again Magoo") for the kind words 😀

There is a slight mistake in your code, which is just a copy/paste thing of referencing Status1 three times...

As for the "old-fart-that-hates-typing-and-reading-lots-of-code trick", it's interesting that the query engine is smart enough to realise (for a large enough set of data) that our two queries are logically identical and it will generate the exact same plan for both (at least it did for 1,000,000 rows) I am constantly in awe of the programmers that develop(ed) that code!

I'm not convinced on the typing thing though - my fingers hate typing MAx( before and ) after each column when I can just type or copy/paste the column list into the group by - but that is (as I just said) immaterial as the engine doesn't care either way! 😎