How to show pivot data without directly using pivot statement?-CodePudding

I am trying to pivoting the data without directly using the pivot function.

I have a simple table t1 which has:

ID              Employee Name
100                Amit
100               Rohan
101               Rohit
102               Pradnya

My expected output is:

100 101  103
2    1    1

I want to achieve this without using pivot. I tried using:

SELECT *
FROM   (SELECT CASE
                 WHEN id = '101' THEN '101'
               END,
               CASE
                 WHEN id = '102' THEN '102'
               END,
               CASE
                 WHEN id = '103' THEN '103'
               END,
               Count(*) cnt
        FROM   t1
        GROUP  BY CASE
                    WHEN id = '101' THEN '101'
                  END,
                  CASE
                    WHEN id = '102' THEN '102'
                  END,
                  CASE
                    WHEN id = '102' THEN '102'
                  END);

How can I achieve the output without pivot?

CodePudding user response：

You are sort of there, try the following:

with s as (
    select id, Count(*) cnt
    from t
    group by id
)
select
    max(case when id=100 then cnt end) as '100',
    max(case when id=101 then cnt end) as '101',
    max(case when id=102 then cnt end) as '102'
from s

See Example Fiddle