Join columns

[Broun]

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!

[nbiswas]

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

[nbiswas]

Solution to the join problem

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.

[amitpatel66]

[code=oracle]
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(DE CODE(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>

[/code]

[Broun]

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

[Broun]

Abit confused

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.

[amitpatel66]

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