CODEWITHSUNDEEP
sqloracle-database
Nvr
Nvr

Reputation: 171

How to get different value of particular id into single row in oracle?

I had an below table

y TABLE

id     val
--     ---
1       A 
1       B 
1       C 
2       B
2       C
3       A

Need output like below

 sno   val1    va2       val3
-----  ----   -----     ------
  1      A     B          C   
  2      B     C          -
  3      A     -          -

I wrote query with self join like

SELECT t1.id as sno, t1.val as val1, t2.val as va2, t3.val as val3
  FROM y t1 JOIN y t2 ON t1.id = t2.id
  JOIN y t3 ON t2.id = t3.id
  ORDER BY 1,2,3,4;

But i returns like Cartesian join. How to get proper result.

Upvotes: 0

Views: 294

Answers (3)

Popeye
Popeye

Reputation: 35900

If you really want to achieve it using the self join then you need to use group by and self join as following:

SELECT t1.id as sno, min(t1.val) as val1, min(t2.val) as va2, min(t3.val) as val3
  FROM y t1 
  LEFT JOIN y t2 ON t1.id = t2.id AND t1.val < t2.val
  LEFT JOIN y t3 ON t2.id = t3.id And t2.val < t3.val
  Group by t1.id

Cheers!!

Upvotes: 1

user5683823
user5683823

Reputation:

Depending on your Oracle version, you can solve this in different ways. You can fix your self-join (you need left outer joins, not inner joins like you have - with inner joins you will only get the id's that have all three values in the input table, even after you fix the rest of the code). You can use conditional aggregation, as Gordon Linoff has shown; this is how pivoting (the name of the operation you are asking about) used to be done in Oracle up to version 10.2. Since version 11.1 you can do the same with the pivot operator.

Since Oracle 12.1, you can do the same with the match_recognize clause. Here is how:

create table y (id, val) as 
  select 1, 'A'  from dual union all
  select 1, 'B'  from dual union all 
  select 1, 'C'  from dual union all
  select 2, 'B'  from dual union all
  select 2, 'C'  from dual union all
  select 3, 'A'  from dual
;

select id, val1, val2, val3
from   y
match_recognize(
  partition by id
  order     by val
  measures  r1.val as val1, r2.val as val2, r3.val as val3
  pattern   (^ r1 r2? r3?)
  define    r1 as null is null
);

  ID VAL1 VAL2 VAL3
---- ---- ---- ----
   1 A    B    C
   2 B    C
   3 A

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can use row_number() and conditional aggregation:

select id,
       max(case when seqnum = 1 then val end) as val_1,
       max(case when seqnum = 2 then val end) as val_2,
       max(case when seqnum = 3 then val end) as val_3
from (select t.*
             row_number() over (partition by id order by val) as seqnum
      from t
     ) t
group by id;

Upvotes: 2

Related Questions