Home » RDBMS Server » Server Administration » Query a few rows in one
Query a few rows in one [message #370603] Wed, 05 January 2000 07:43 Go to next message
Rob
Messages: 70
Registered: January 2000
Member
Hello i need help with the following. For a report i need to select the days of the week that a sertan thing happens. When i use a normal query i get 0 - 7 rows. ( per week of the day that it takes place one, example:
Mo
TH
SU
)
Now i want the same result in one row. So:
Mo TH SU

Thx for all the help i already got.
Re: Query a few rows in one [message #370604 is a reply to message #370603] Wed, 05 January 2000 07:58 Go to previous messageGo to next message
beesetty
Messages: 3
Registered: December 1999
Location: Singapore
Junior Member

try with decode

like
decode(day,'th',day),decode(day,'we',day)

I hope it will work

Bye
Murali
It didn't help [message #370605 is a reply to message #370604] Wed, 05 January 2000 09:02 Go to previous messageGo to next message
Rob
Messages: 70
Registered: January 2000
Member
I still have no solution for the problem.

If my normal statement returns 5 rows an d one column
i now want it in 5 columns and 1 row.
Re: Query a few rows in one [message #370606 is a reply to message #370603] Wed, 05 January 2000 09:45 Go to previous messageGo to next message
Thierry
Messages: 9
Registered: January 2000
Junior Member
I used a test table, so you need to change to your table. I hope this can help you.

create table temp1 (var1 varchar2(3));

insert into temp1(var1) values ('MON');
insert into temp1(var1) values ('WED');
insert into temp1(var1) values ('FRI');
insert into temp1(var1) values ('SAT');
insert into temp1(var1) values ('SUN');

SELECT MAX(day1)||
MAX(day2)||
MAX(day3)||
MAX(day4)||
MAX(day5)||
MAX(day6)||
MAX(day7) All_days_week
FROM (SELECT 1 group_it
,DECODE(var1,'MON',' MON',NULL) day1
,DECODE(var1,'TUE',' TUE',NULL) day2
,DECODE(var1,'WED',' WED',NULL) day3
,DECODE(var1,'THR',' THR',NULL) day4
,DECODE(var1,'FRI',' FRI',NULL) day5
,DECODE(var1,'SAT',' SAT',NULL) day6
,DECODE(var1,'SUN',' TUE',NULL) day7
FROM temp1)
GROUP BY group_it
;

RESULT
******************

ALL_DAYS_WEEK
----------------------------
MON WED FRI SAT TUE
Re: It didn't help [message #370615 is a reply to message #370605] Fri, 07 January 2000 11:49 Go to previous message
Ed Sleath
Messages: 1
Registered: January 2000
Junior Member
A tricky one, but ...
I created a table (temp) with a column (day1) containing the following data:

DAY1
----
MON
TUE
THU

If you run the following SQL:

select
max(decode(day1,'MON','MON ',null))||
max(decode(day1,'TUE','TUE ',null))||
max(decode(day1,'WED','WED ',null))||
max(decode(day1,'THU','THU ',null))||
max(decode(day1,'FRI','FRI ',null))||
max(decode(day1,'SAT','SAT ',null))||
max(decode(day1,'SUN','SUN',null)) days
from temp

You get:

DAYS
---------------------------
MON TUE THU

Any use to you?

Ed
Previous Topic: DBMS_OUTPUT
Next Topic: Blank space insertion into a Database field of VARCHAR2(1)
Goto Forum:
  


Current Time: Fri Oct 30 03:32:55 CDT 2020