How To Get The Transpose In Sql (hana)?

I have a query which returns the table which is like SELECT (CASE WHEN type=1 THEN 'A' WHEN type=2 THEN 'B' END) as TYPE,COUNT(*) AS COUNT

Solution 1:

Since there are only 3

you could just

SELECTSUM(CASEWHEN TYPE =1THEN1ELSE0END) AS A,
       SUM(CASEWHEN TYPE =2THEN1ELSE0END) AS B,
       SUM(CASEWHEN TYPE =3THEN1ELSE0END) AS C
FROM yourTable

Solution 2:

A pure SQL solution (SQL Server)

select 
  [A] 
  , [B]
  , [C]
from
(
  select Type,[Count]
  from ( SELECT (CASEWHEN type=1THEN'A' WHEN type=2THEN'B' 
             .....   
       END) as TYPE,COUNT(*) AS COUNT
        from TYPE_TABLE GROUPBY TYPE) Table1
) x
pivot
(
  SUM(Count)
  for Type in([A], [B], [C])
)p

Demo

Solution 3:

I know this is a late reply, but u can certainly create 3 restricted measures in a calculation view on column "count" and filter for types - A,B,C.

TA-DA, rows would be transformed to columns.