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 |
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.
|
|
|
|
It didn't help [message #370605 is a reply to message #370604] |
Wed, 05 January 2000 09:02 |
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 |
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 |
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
|
|
|
Goto Forum:
Current Time: Thu Jun 27 13:22:57 CDT 2024
|