CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Lijo
Lijo

Reputation: 6778

Select and display data as column header

I want to select region wise data and want to show each region as column header.

My data:

region state   status
North   UP     invalid
North   HAR    invalid
North   MAH     valid
South   KER     valid
South   TAM     valid
South   KAR     valid
South   KAR     invalid

Output:

Status   North  south  total 
invalid   2      1      3
valid     1      3      4
total     3      4      7

How to do this? This regions can be update later new region can be added later

Is there any way to take all the active regions first then use this in the query activeregions

Upvotes: 2

Views: 3056

Answers (3)

Lijo
Lijo

Reputation: 6778

METHOD 1 Please check SQLFIDDLE

FIRST STEP

Separately find out the count for active and inactive regions

    SELECT  'ACTIVE' "STATUS",NVL(REGION,'TOTAL') REGION,
         COUNT(REGION) REGCOUNT
  FROM TEMPPIVOTTEST
  where STATUS like 'ACTIVE'
  GROUP BY ROLLUP (REGION)

enter image description here

 SELECT  'INACTIVE' "STATUS",NVL(REGION,'TOTAL') REGION,
         COUNT(REGION) REGCOUNT
  FROM TEMPPIVOTTEST
   where STATUS like 'INACTIVE'
  GROUP BY ROLLUP (REGION)

enter image description here

In above queries we use GROUP By ROLLUP so the last row we will get the sum of all other rows

SECOND STEP

JOIN BOTH this queries AND use Pivot 

     SELECT NVL(STATUS,'GRAND TOTAL') STATUS,SUM(EAST) EAST,SUM(NORTH) NORTH,SUM(SOUTH) SOUTH,SUM(TOTAL) TOTAL from  
      ( SELECT  'ACTIVE' "STATUS",NVL(REGION,'TOTAL') REGION,
         COUNT(REGION) REGCOUNT
  FROM TEMPPIVOTTEST
  where STATUS like 'ACTIVE'
  GROUP BY ROLLUP (REGION)
  union
  SELECT  'INACTIVE' "STATUS",NVL(REGION,'TOTAL') REGION,
         COUNT(REGION) REGCOUNT
  FROM TEMPPIVOTTEST
   where STATUS like 'INACTIVE'
  GROUP BY ROLLUP (REGION)
  )pivot (SUM(REGCOUNT) FOR (REGION) IN ('EAST' EAST,'NORTH' NORTH,'SOUTH' SOUTH,'TOTAL' TOTAL))
  GROUP BY ROLLUP (STATUS)

enter image description here

In pivot we need to give the regions which are present in table

METHOD 2 SQLFIDDLE

Another way for doing the same above query we have specified the status hard corded 'Active' and 'inactive' instead of that that we can use following query

   SELECT nvl(STATUS,'GRAND_TOTAL') STATUS,sum(EAST)EAST,sum(NORTH)NORTH,sum(SOUTH)SOUTH,sum(TOTAL) TOTAL from 
     (SELECT  STATUS,REGION,
         COUNT(REGION) REGCOUNT
  FROM TEMPPIVOTTEST
  GROUP BY  (STATUS,REGION)
  union
  SELECT  STATUS,'TOTAL'REGION,
         COUNT(REGION) REGCOUNT
  FROM TEMPPIVOTTEST
  GROUP BY (STATUS)

  )pivot (SUM(REGCOUNT) FOR (REGION) IN ('EAST' EAST,'NORTH' NORTH,'SOUTH' SOUTH,'TOTAL' Total))
  group by rollup (status)

oracle 10 g pivot will not work so we can make use of the case statement

select nvl(status,'total')status,SUM(NORTH) NORTH,SUM(SOUTH)SOUTH,SUM(EAST)EAST,sum(TOTAL)TOTAL from (select status,
   sum(case when region = 'NORTH' then 1 else 0 end) as "NORTH",
   sum(case when region = 'SOUTH' then 1 else 0 end) as "SOUTH",
   sum(case when region = 'EAST' then 1 else 0 end) as "EAST",
   sum(case when region  in('') then 0 else 1 end) as "TOTAL"
from  TEMPPIVOTTEST
group by status
)group by ROLLUP (STATUS)

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

SQL is designed to get you records with a before-known number of columns. So you cannot get the data in the desired output format directly. There are two options mainly:

1) Select the aggregated data in rows:

select region, status, count(*)
from mytable
group by region, status
order by region, status;

Then let your app display the data in columns. (Loop and fetch data, build the columns in your GUI grid according to the data, i.e. add a new column in your grid each time you get a new region.)

2) Select distinct regions first, then select the data:

select distinct region
from mytable;

Now, that you got 'North' and 'South', let your app build the following query dynamically:

select status as "Status"
  , sum(case when region = 'North' then 1 else 0 end) as "North"
  , sum(case when region = 'South' then 1 else 0 end) as "South"
from mytable
group by status;

Upvotes: 0

jarlh
jarlh

Reputation: 44766

select status,
       count(case when region = 'North' then 1 end) as North,
       count(case when region = 'South' then 1 end) as South
from tablename
group by status

Alternative solution, that can handle new regions later added:

select region, status, count(*)
from tablename
group by region, status

Another solution, that also can handle new regions later added:

select region,
       count(case when status = 'valid' then 1 end) as valid,
       count(case when status = 'invalid' then 1 end) as invalid
from tablename
group by region

Upvotes: 2

Related Questions