ORACLE PL SQL

How to Display Column Names as Rows in Oracle PL SQL

Use UNION:

SELECT 'Count1' AS "Count", SUM (DECODE(COLUMN1,'Y',1,0)) AS Total 
FROM TABLE_NAME WHERE COLUMN7 IN ('X','Y')
UNION ALL
SELECT 'Count2' AS "Count", SUM (DECODE(COLUMN2,'Y',1,0)) AS Total 
FROM TABLE_NAME WHERE COLUMN7 IN ('X','Y')
UNION ALL
SELECT 'Count3' AS "Count", SUM (DECODE(COLUMN3,'Y',1,0)) AS Total 
FROM TABLE_NAME WHERE COLUMN7 IN ('X','Y')
UNION ALL
...

However, if you have a column to group by, you can just group by it and use the sum in one query.


UPDATE:

You can do this also by using UNPIVOT:

SELECT *
FROM
(
  SELECT   
  SUM (DECODE(COLUMN1,'Y',1,0)) AS COUNT1,  
  SUM (DECODE(COLUMN2,'Y',1,0)) AS COUNT2,  
  SUM (DECODE(COLUMN3,'Y',1,0)) AS COUNT3,  
  SUM (DECODE(COLUMN4,'Y',1,0)) AS COUNT4,  
  SUM (DECODE(COLUMN5,'Y',1,0)) AS COUNT5,  
  SUM (DECODE(COLUMN6,'Y',1,0)) AS COUNT6   
  FROM TABLE_NAME  
  WHERE COLUMN7 IN ('X','Y')
)
UNPIVOT
(
  Total
  FOR COL IN(COUNT1,COUNT2,COUNT3,COUNT4,COUNT5,COUNT6)
)

LEAVE A REPLY

Please enter your comment!
Please enter your name here