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!
-
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>
-
-
6 1827
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
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. -
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>
-
-
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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Xenophobe |
last post by:
I know this isn't a MySQL forum, but my question is related to a PHP
project.
I have two tables.
table1
table2
"table1" contains 2 columns, ID and FirstName:
|
by: Preston Landers |
last post by:
Hello all.
I am trying to write a query that "just" switches some data around so
it is shown in a slightly different format. I am already able to do
what I want in Oracle 8i, but I am having...
|
by: stabbert |
last post by:
I am attempting to join two tables together on two different unix
servers. Here is some relevant info about the tables.
TABLE 1 Setup
-----------------------
DB2 UDB 7.2 EE
|
by: davegb |
last post by:
I'm trying to create a self-join table to show the relationship between
employee and supervisor. In another thread, I was advised to create a
SupervisorID in the employee table, a separate...
|
by: Bennett Haselton |
last post by:
I know how to create a DataAdapter that loads data from a data source
into a table in a typed DataSet, and how to set the DataSource and
DataMember properties of a DataGrid so that at run time it...
|
by: Chris |
last post by:
I'm using ASP.NET and SQL Server and this might be an obviuos question for
most, but if I have a table that contains several fields that I need to
relate to just one field in another table, how do...
|
by: PW |
last post by:
I've created an ASP application which uses an Access database.
I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should...
|
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible...
|
by: Jane T |
last post by:
I appreciate how difficult it is to resolve a problem without all the
information but maybe someone has come across a similar problem.
I have an 'extract' table which has 1853 rows when I ask for...
|
by: Annyka |
last post by:
Platform: SQL Server 2000 (8.00.2040, SP4, Enterprise edition)
I've got a complex query I'm trying to build, which will select all requests that have a status_code of 1, and who's related incident...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |