Oracle 10g SQLPlus – How to Add Multiple Rows in a Single Row with Different Columns

oracle-10gsqlplus

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!

Best Answer

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