CODEWITHSUNDEEP
sqloracle-database
pramodh
pramodh

Reputation: 1329

Convert row values into columns based on row values multi level hierarchy

I have a ORACLE table like this 

---------------------------------------
DATE        |  USERID | DOMAIN | VALUE
---------------------------------------
03/16/2016     1001     ASIA      10
03/16/2016     1001     EUROPE    20
03/16/2016     1002     ASIA      20
03/17/2016     1001     ASIA      20
03/17/2016     1002     EUROPE    10
----------------------------------------

I want to translate this table to a view something like this

 -------------------------------------
 DATE       |  USERID | ASIA | EUROPE  
 -------------------------------------
 03/16/2016     1001     10     20
 03/16/2016     1002     20      
 03/17/2016     1001     20
 03/17/2016     1002            10 
 -------------------------------------

If I tried to use PIVOT I can do it on a user level, but don't know how to get the date, user level. Any pointers would be great.

Upvotes: 0

Views: 351

Answers (3)

jarlh
jarlh

Reputation: 44796

Do a GROUP BY, use case expression to chose Asia or Europe:

select DATE, USERID,
       sum(case when DOMAIN = 'ASIA' then VALUE end) as asia
       sum(case when DOMAIN = 'EUROPE' then VALUE end) as europe
from tablename
group by DATE, USERID

Answer for products not supporting modern Oracle versions' PIVOT.

Upvotes: 1

Aleksej
Aleksej

Reputation: 22959

PIVOT seems to achieve the result you need:

SQL> with test (DATE_, USERID, DOMAIN, VALUE)
  2  as (
  3   select '03/16/2016',     1001     ,'ASIA'      ,10 from dual union all
  4   select '03/16/2016',     1001     ,'EUROPE'    ,20 from dual union all
  5   select '03/16/2016',     1002     ,'ASIA'      ,20 from dual union all
  6   select '03/17/2016',     1001     ,'ASIA'      ,20 from dual union all
  7   select '03/17/2016',     1002     ,'EUROPE'    ,10 from dual
  8  )
  9  SELECT *
 10  FROM   (select *
 11          from test)
 12  PIVOT ( sum(value) FOR (domain) IN ('ASIA', 'EUROPE'))
 13  ORDER BY 1, 2;

DATE_          USERID     'ASIA'   'EUROPE'
---------- ---------- ---------- ----------
03/16/2016       1001         10         20
03/16/2016       1002         20
03/17/2016       1001         20
03/17/2016       1002                    10

Upvotes: 2

Thomas
Thomas

Reputation: 203

You can use the PIVOT function with 

pivot 
(
   count(VALUE)
   for DOMAIN in ("ASIA", "EUROPE")
)

, wrap the query in a subselect and then group the result by UserID.

Upvotes: 0

Related Questions