Oracle Query based on data to horizontal

Question

I want to query these below data sample. (Oracle database)

table name: FL_TEST

NAMA    JAM  STATUS
A       2    P
A       1    O
A       3    O
B       1    O
B       2    P
B       3    O
C       1    O
C       2    P
C       3    P

to be like this:

NAMA    JAM1 JAM2 JAM3
A       O    P    O
B       O    P    O
C       O    P    P

Is it possible to do that query?

Zaynul Abadin Tuhin · Accepted Answer · 2019-11-05T07:50:44.347

3

use conditional aggregaiton

select NAMA ,max(case when jam=1 then status end) as jam1,
max(case when jam=2 then status end) as jam2,
max(case when jam=3 then status end) as jam3
from FL_TEST group by NAMA

or you can use pivot

SELECT *
  FROM FL_TEST
PIVOT (
  MAX(status) as JAM FOR jam IN (1,2,3)
 );

edited Nov 05 '19 at 07:50

answered Nov 05 '19 at 07:37

Zaynul Abadin Tuhin

31,407
5
33
63

How about JAM1, JAM2, JAM3 is auto from data? – HiDayurie Dave Nov 05 '19 at 07:41
@HiDayurieDave that would require dynamic SQL – Nick Nov 05 '19 at 07:49
Is it possible? We can't handle JAM manually. – HiDayurie Dave Nov 05 '19 at 07:51

score 2 · Answer 2 · edited Nov 05 '19 at 08:20

2

You can use Conditional Aggregation :

select nama, 
       max( case when jam = 1 then status end ) as jam1,
       max( case when jam = 2 then status end ) as jam2,
       max( case when jam = 3 then status end ) as jam3
  from tab
 group by nama

or pivot :

select * 
  from tab
 pivot(
       max(value) for field in ( 1 as "Jam1", 
                                 2 as "Jam2",
                                 3 as "Jam3" ) )

Demo

edited Nov 05 '19 at 08:20

Zaynul Abadin Tuhin

31,407
5
33
63

answered Nov 05 '19 at 07:38

Barbaros Özhan

59,113
10
31
55

How about JAM1, JAM2, JAM3 is auto from data? – HiDayurie Dave Nov 05 '19 at 07:41
dynamical case is not possible by only using Oracle's sql @HiDayurieDave – Barbaros Özhan Nov 05 '19 at 07:56
Is that mean we need to use another table or do on programming? – HiDayurie Dave Nov 05 '19 at 08:04
it could be figured out by dynamic SQL, or by using some array methods within PLSQL @HiDayurieDave – Barbaros Özhan Nov 05 '19 at 08:53
1

thank you bro for the edit @Zaynul Abadin Tuhin – Barbaros Özhan Nov 06 '19 at 07:19

Oracle Query based on data to horizontal

2 Answers2