Join columns | Newbie | | Join Date: Sep 2006
Posts: 5
| |
i WANTO JOIN A TABLE ON ITSELF (IS THAT WHAT IT IS, I DONT KNOW).
I HAVE A TABLE SAY: -
TIME READING READER
-
=============================================
-
10:15 8 A
-
10:20 11 A
-
10:25 9 A
-
10:30 7 A
-
10:10 875 B
-
10:20 431 B
-
10:30 678 B
-
10:11 28.3 C
-
10:16 31.9 C
-
10:41 25.9 C
-
I want to extract the following table -
TIME READER_A READER_B READER_C
-
-=======================================================
-
10:10 875
-
10:11 28.3
-
10:15 8
-
10:16 31.9
-
10:20 11 431
-
10:25 9
-
10:30 7 678
-
10:41 25.9
-
Tried a couple of methods and am now lost. Any help guys!
| |
best answer - posted by amitpatel66 | -
SQL> select tt Time, rd reading, rdr reader FROM test11;
-
TIME READING READER
-
----------------------------------------------------------------------------------------------------
-
10:15 8 A
-
10:20 11 A
-
10:20 111 B
-
-
SQL> SELECT tt Time, MAX(DECODE(rdr,'A',rd,NULL)) READER_A,MAX(DECODE(rdr,'B',rd,NULL)) READER_B FRO
-
M test11 GROUP BY tt;
-
-
TIME READER_A READER_B
-
----------------------------------------------------------------------------------------------------
-
10:15 8
-
10:20 11 111
-
-
SQL>
-
-
|  | Member | | Join Date: May 2009 Location: India
Posts: 35
| | | re: Join columns
Try this - select times , case when reader='A' then reading end as READER_A
-
,case when reader='B' then reading end as READER_B
-
,case when reader='C' then reading end as READER_C
-
from mytbl
-
order by times
Not tested though
.Hope this helps
| | Member | | Join Date: May 2009 Location: India
Posts: 35
| | | re: Join columns
Ignore the earlier query which does not takes care of merging the rows based on dates.
Try this - select
-
case when t1.ReadingTime is null and t3.ReadingTime is null then t2.ReadingTime
-
when t1.ReadingTime is null and t2.ReadingTime is null then t3.ReadingTime
-
else t1.ReadingTime end as Time ,
-
t1.Reading as Reader_A ,
-
t2.Reading as Reader_B ,
-
t3.Reading as Reader_C
-
from
-
(select ReadingTime, Reading,Reader from tblMeterReading where Reader = 'A') t1
-
full outer join
-
(select ReadingTime, Reading,Reader from tblMeterReading where Reader = 'B') t2
-
on t1.ReadingTime = t2.ReadingTime
-
full outer join
-
(select ReadingTime, Reading,Reader from tblMeterReading where Reader = 'C') t3
-
on t1.ReadingTime = t3.ReadingTime and t2.ReadingTime = t3.ReadingTime
-
order by ReadingTime
Let me know in case of any concern.I have tested this and found to be working as per the data given. Note:- Change the table name and respective columns as per your requirement. |  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Join columns
-
SQL> select tt Time, rd reading, rdr reader FROM test11;
-
TIME READING READER
-
----------------------------------------------------------------------------------------------------
-
10:15 8 A
-
10:20 11 A
-
10:20 111 B
-
-
SQL> SELECT tt Time, MAX(DECODE(rdr,'A',rd,NULL)) READER_A,MAX(DECODE(rdr,'B',rd,NULL)) READER_B FRO
-
M test11 GROUP BY tt;
-
-
TIME READER_A READER_B
-
----------------------------------------------------------------------------------------------------
-
10:15 8
-
10:20 11 111
-
-
SQL>
-
-
| | Newbie | | Join Date: Sep 2006
Posts: 5
| | | re: Join columns
This looks good, Mr. Patel. I have tried it, deleted some Null values, looks good.
Will have to check if data was left out or mixed up or repeated.
Regards
| | Newbie | | Join Date: Sep 2006
Posts: 5
| | | re: Join columns
Mr.Patel I have alot of Data here and the sql is not tranparent to me. Max(Decode ...) is difficult to understand. Max to me should give me maximum only and Decode compares searches. So my actual sql looks something like this. Does it make sense? -
-
SELECT READ_TIME_STAMP,
-
MAX(DECODE(DATA_POINT_ID, 68, value, NULL)) co2,
-
MAX(DECODE(DATA_POINT_ID,'154',value,NULL)) humidity,
-
MAX(DECODE(DATA_POINT_ID,86,value,NULL)) radiance,
-
MAX(DECODE(DATA_POINT_ID,118,value,NULL)) room_temp,
-
MAX(DECODE(DATA_POINT_ID,994,value,NULL)) air_humid,
-
MAX(DECODE(DATA_POINT_ID,400,value,NULL)) airtemp,
-
MAX(DECODE(DATA_POINT_ID,706,value,NULL)) wind_dir,
-
MAX(DECODE(DATA_POINT_ID,303,value,NULL)) wind_speed,
-
MAX(DECODE(DATA_POINT_ID,221,value,NULL)) solar_temp,
-
MAX(DECODE(DATA_POINT_ID,503,value,NULL)) dm_uf3,
-
MAX(DECODE(DATA_POINT_ID,512,value,NULL)) dm_uf4
-
FROM Read_Sensors GROUP BY READ_TIME_STAMP;
-
-
The data has lots of duplicate and null values. So i run a sript to delete where all row values are null.
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Join columns
Please post the actual data that you are trying to query? that would help me check if the query you prepared is correct or not
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
