UNPIVOT SQL SERVER OPTION

Sometimes reading about new features of the products you are using help a lot.

Using UNPIVOT I managed to reduce the length of a query from 8 seconds to about 650 - 700 ms.
The old query was doing a UNION between data based on data from some columns. The initial Query was doing this (data changed for privacy reasons):

SELECT country
, year
, SUM(cases) AS nbCases
, isNotValidSomething
, isValidSomething
, 'some stuff' AS ResultType
, 1 AS SortOrder
, CASE Column01 WHEN 'S' THEN Sum (cases) ELSE 0 END AS Sensitive, 
CASE Column01 WHEN 'R' THEN Sum (cases) ELSE 0 END AS Resistant, 
CASE Column01 WHEN 'Unk' THEN SUM (cases) ELSE 0 END AS Unknown
FROM vAll 
WHERE 
Country  = @Country 
AND Year =@Year 
AND Column01  IS NOT NULL
GROUP BY country, year, isNotValidSomething, isValidSomething
, Column01
, rType
UNION
SELECT country
, year
, SUM(cases) AS nbCases
, isNotValidSomething
, isValidSomething
, 'some other stuff' AS ResultType
, 2 AS SortOrder
,CASE Column02 WHEN 'S' THEN Sum (cases) ELSE 0 END AS Sensitive, 
CASE Column02 WHEN 'R' THEN Sum (cases) ELSE 0 END AS Resistant, 
CASE Column02 WHEN 'Unk' THEN SUM (cases) ELSE 0 END AS Unknown
FROM vAll 
WHERE Country  = @Country 
AND Year =@Year 
AND Column01  IS NOT NULL
GROUP BY country, year, isNotValidSomething, isValidSomething
, Column02, rType

UNION
....

(there are 8 queries like this in UNION)

The new query is doing :

SELECT country, year
, SUM(cases) AS nbCases
, isNotValidSomething, isValidSomething, ID, Cols FROM vAll
UNPIVOT (
ID FOR Cols in ( Column01
,Column02
,Column03
,Column04
,Column05
,Column06
,Column07
,Column08
)
) up
WHERE country = @Country
AND year = @Year
GROUP BY country, year, isNotValidSomething, isValidSomething, up.ID, Cols
ORDER BY country, year desc, Cols


Thanks to this post.

All best,
Radu