2

I want to join two tables (they are called Registers and Reads). As a result I would like to obtain 4 columns, corresponding to Registers ID.

According to the next example, by joining the tables, I obtain 2 rows. There could be some cases that I may find/obtain 4 rows because they exist in Reads table.

Table: Registers

    ID      Number    Prod_ID
    331       01       112233
    332       02       112233
    333       03       112233
    334       04       112233

Table: Reads

    Read_Id    Register_Id  
      011          331
      012          332

I use this query to link join both tables:

    SELECT rg.ID 
    FROM Reads rd LEFT JOIN Registers rg on rd.Register_ID = rg.ID
    WHERE rg.Prod_ID = 112233;

My result is next one:

    ID
    331
    332

What I really want is to obtain just one row, assigning the first result to the first column, second one to the second and so on. In addition, I would like to add an extra column that shows how many columns contain information.

Expected result:

    RegisterID1    RegisterID2    RegisterID3    RegisterID4    Count
        331            332                                        2

Is there any easy way to do this? Thank you very much!

1

You are on 10g, so PIVOT is unavailable, but still have the good old sum(case ...).

drop table reads purge;
drop table registers purge;

create table registers (id number,  n varchar2(2), prod_id number);
insert into registers values (331, '01', 112233);
insert into registers values (332, '02', 112233);
insert into registers values (333, '03', 112233);
insert into registers values (334, '04', 112233);
commit;

create table reads (read_id varchar2(3), register_id number);
insert into reads values ('011', 331);
insert into reads values ('012', 332);
commit;

SELECT
  sum(case when rg.n = '01' then rd.register_id end) as "RegisterID1",
  sum(case when rg.n = '02' then rd.register_id end) as "RegisterID2",
  sum(case when rg.n = '03' then rd.register_id end) as "RegisterID3",
  sum(case when rg.n = '04' then rd.register_id end) as "RegisterID4",
  count(rd.register_id) as "Count"
FROM Reads rd RIGHT JOIN Registers rg on rd.Register_ID = rg.ID
WHERE rg.Prod_ID = 112233;

RegisterID1 RegisterID2 RegisterID3 RegisterID4      Count
----------- ----------- ----------- ----------- ----------
        331         332                                  2
0

Balazs Papp's solution works perfectly. I have edited my next question, which shows the tables (create, instert...)

I would like to add a new field to the table: date_reading

Read_Id    Register_Id      Date
  011          331       14-Apr-2018
  012          332       21-Apr-2018

The result I want would be the next one:

RgstrID1 |  D1       |RgstrID2  |  D2       |RgistrID3|D3|RgistrID4|D4|Count
    331  |14-Apr-2018|     332  |21-Apr-2018|         |  |         |  |  2

Tables would be

create table registers (id number,  n varchar2(2), prod_id number);
insert into registers values (331, '01', 112233);
insert into registers values (332, '02', 112233);
insert into registers values (333, '03', 112233);
insert into registers values (334, '04', 112233);

create table reads (read_id varchar2(3), register_id number, Date_time timestamp);
insert into reads values ('011', 331, '14-Apr-2018');
insert into reads values ('012', 332, '21-Apr-2018');

Is it possible to do it?

Here there is the Fiddle link

  • Please ask a new question, providing your sample data in the form of DDL (CREATE TABLE blah ...) and DML (INSERT INTO blah VALUES ...) or as a fiddle. – Vérace May 2 '18 at 5:36
  • I have edited my second question. In case it is needed to ask a new one, please could you let me know? – Eka May 2 '18 at 9:43
  • What I suggest you do is: ask a second separate question but include a link back to this one. Provide links to all fiddles (very useful for those of us answering questions). And I'm going to give a +1 for giving that - if only all posters would use these tools! – Vérace May 2 '18 at 10:49
  • This is my question then:dba.stackexchange.com/questions/205561/… – Eka May 2 '18 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.