tripleAxe (9/15/2016)
Here is the UNION ALL query using a temp table so that we can have one pass at the input table as suggested by The Dixie Flatline in an earlier post.
I added this method to the test harness and also the one below which uses a tally table to get a single scan.
So far the three methods which are fastest are the Pre aggregated, Temp table and the Tally methods, slightly depending on the number of columns.
😎
;WITH BASE_DATA AS
(
SELECT
SUM(TUA.COL001) AS COL001
, SUM(TUA.COL002) AS COL002
, SUM(TUA.COL003) AS COL003
, SUM(TUA.COL004) AS COL004
, SUM(TUA.COL005) AS COL005
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
,NUMS(N) AS (SELECT N FROM (VALUES
( 1),( 2),( 3),( 4),( 5)
) AS X(N))
,UNPIVOTED_SET(COL,VAL) AS
(
SELECT
CASE
WHEN NM.N = 1 THEN 'COL001'
WHEN NM.N = 2 THEN 'COL002'
WHEN NM.N = 3 THEN 'COL003'
WHEN NM.N = 4 THEN 'COL004'
WHEN NM.N = 5 THEN 'COL005'
END AS COL
,CASE
WHEN NM.N = 1 THEN BD.COL001
WHEN NM.N = 2 THEN BD.COL002
WHEN NM.N = 3 THEN BD.COL003
WHEN NM.N = 4 THEN BD.COL004
WHEN NM.N = 5 THEN BD.COL005
END AS VAL
FROM BASE_DATA BD
CROSS JOIN NUMS NM
)
SELECT
@VCHAR_BUCKET = US.COL
,@INT_BUCKET = US.VAL
FROM UNPIVOTED_SET US;