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?
Advertisement
Answer
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