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

Question

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.

score 2 · Accepted Answer · answered Mar 17 '16 at 14:07

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

jarlh · Answer 2 · 2016-03-17T14:09:31.193

1

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.

edited Mar 17 '16 at 14:09

answered Mar 17 '16 at 13:59

jarlh

42,561
8
45
63

Oracle 11.2 allows PIVOT on multiple columns: http://stackoverflow.com/a/11988799/477563 – Mr. Llama Mar 17 '16 at 14:01

score 0 · Answer 3 · answered Mar 17 '16 at 14:01

0

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.

answered Mar 17 '16 at 14:01

Thomas

213
2
9

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

3 Answers3