SQL: alternatives and substitutions for GROUPING SETS and PIVOT

Question

I've got code like this:

SELECT id, YEAR(datek) AS YEAR, COUNT(*) AS NUM
FROM Orders
GROUP BY GROUPING SETS
(
    (id, YEAR(datek)),
    id,
    YEAR(datek),
    ()
);

It gives me this output:

1   NULL    4
2   NULL    11
3   NULL    6
NULL    NULL    21
1   2006    36
2   2006    56
3   2006    51
NULL    2006    143
1   2007    130
2   2007    143
3   2007    125
NULL    2007    398
1   2008    79
2   2008    116
3   2008    73
NULL    2008    268
NULL    NULL    830
1   NULL    249
2   NULL    326
3   NULL    255

What I need to do is write it without "grouping sets" (nor cube or rollup) but with the same result. I thought about writing three different queries and join them with "union". I try something like "null" in group by settings but it does not work.

SELECT id, YEAR(datek) AS rok, COUNT(*) AS NUM
FROM Orders
GROUP BY id, YEAR(datek)
UNION
SELECT id, YEAR(datek) AS rok, COUNT(*) AS NUM
FROM Orders
GROUP BY id, null
order by id, YEAR(datek)

I also have a question about "PIVOT". What kind of syntax can replace query with "PIVOT"?

Thanks for your time and all the answers!

Answer 1

You are right in that you need separate queries, although you actually need 4, and rather than GROUP BY NULL, just group by the columns in the corresponding grouping set, and replace the column in the SELECT with NULL:

SELECT id, YEAR(datek) AS rok, COUNT(*) AS NUM
FROM Orders
GROUP BY id, YEAR(datek)

UNION ALL

SELECT id, NULL, COUNT(*) AS NUM
FROM Orders
GROUP BY id

UNION ALL

SELECT NULL, YEAR(datek), COUNT(*) AS NUM
FROM Orders
GROUP BY YEAR(datek)

UNION ALL

SELECT NULL, NULL, COUNT(*) AS NUM
FROM Orders
ORDER BY ID, Rok

With regard to a replacement for PIVOT I think the best alternative is to use a conditional aggregate, e.g. instead of:

SELECT  pvt.SomeGroup,
        pvt.[A],
        pvt.[B],
        pvt.[C]
FROM    T
        PIVOT (SUM(Val) FOR Col IN ([A], [B], [C])) AS pvt;

You would use:

SELECT  T.SomeGroup,
        [A] = SUM(CASE WHEN T.Col = 'A' THEN T.Val ELSE 0 END),
        [B] = SUM(CASE WHEN T.Col = 'B' THEN T.Val ELSE 0 END),
        [C] = SUM(CASE WHEN T.Col = 'C' THEN T.Val ELSE 0 END)
FROM    T
GROUP BY T.SomeGroup;