Combining multiple rows into one row, Oracle

Question

I'm working with a database which is created in Oracle and used in a GIS-software through SDE. One of my colleuges is going to make some statistics out of this database and I'm not capable of finding a reasonable SQL-query for getting the data.

I have two tables, one with registrations and one with registrationdetails. It's a one to many relationship, so the registration can have one or more details connected to it (no maximum number).

1. table: Registration

RegistrationID          Date       TotLenght
1                    01.01.2010        5
2                    01.02.2010        15
3                    05.02.2009        10

2.table: RegistrationDetail

DetailID     RegistrationID   Owner      Type      Distance
1                  1           TD          UB          1,5
2                  1           AB          US          2
3                  1           TD          UQ          4
4                  2           AB          UQ         13
5                  2           AB          UR         13,1
6                  3           TD          US          5

I want the resulting selection to be something like this:

RegistrationID          Date       TotLenght DetailID     RegistrationID   Owner     Type      Distance  DetailID     RegistrationID   Owner      Type      Distance  DetailID     RegistrationID   Owner      Type      Distance
1                    01.01.2010        5         1              1           TD        UB          1,5          2               1          AB        US          2         3                  1              TD          UQ          4
2                    01.02.2010        15        4              2           AB        UQ         13            5               2          AB        UR         13,1
3                    05.02.2009        10        6              3           TD        US          5

With a normal join I get one row per each registration and detail. Can anyone help me with this? I don't have administrator-rights for the database, so I can't create any tables or variables. If it's possible, I could copy the tables into Access.

Answer 1

SELECT   
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
    wo_h.created_by AS WorkOrderCreator,
    wo_h.issue_date As Work_Order_IssueDate, 
    wo_h.ac_registr As WorkOrderACRegister,
    wo_h.state As WorkOrderState,

('Created By:'||wsl.workstep_sign||'  On') As Description,

wsl.workstep_date,




listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)   
   FROM workstep_link wsl
 join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
  join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i

WHERE
wo_h.state = 'O'


      AND wo_h.event_perfno_i = '5690136'
     AND wo_h.ac_registr = 'AEC'
    GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
    wsl.workstep_time,
    wsl.workstep_sign

Answer 2

SELECT   
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
    wo_h.created_by AS WorkOrderCreator,
    wo_h.issue_date As Work_Order_IssueDate, 
    wo_h.ac_registr As WorkOrderACRegister,
    wo_h.state As WorkOrderState,
    ('Created By:'||wsl.workstep_sign||'  On') As Description,
    wsl.workstep_date,
     listagg('Action Performed By,' ||woa.mutator||'At date' ||       



    SELECT   
    wo_h.event_perfno_i AS WO,
    wo_h.ata_chapter,
        wo_h.created_by AS WorkOrderCreator,
    	wo_h.issue_date As Work_Order_IssueDate, 
    	wo_h.ac_registr As WorkOrderACRegister,
    	wo_h.state As WorkOrderState,

    ('Created By:'||wsl.workstep_sign||'  On') As Description,

    wsl.workstep_date,




    listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)   
       FROM workstep_link wsl
     join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
      join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i

    WHERE
    wo_h.state = 'O'
 

          AND wo_h.event_perfno_i = '5690136'
         AND wo_h.ac_registr = 'AEC'
        GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
        wsl.workstep_time,
        wsl.workstep_sign
    
    




    woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)   
       FROM workstep_link wsl
     join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
      join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i

    WHERE
    wo_h.state = 'O'
       AND wo_h.event_perfno_i = '5690136'
     AND wo_h.ac_registr = 'AEC'
    GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
    wsl.workstep_time,
    wsl.workstep_sign

Answer 3

If the maximum number of Detail records is fixed and known then this can be done. The larger the number the more tedious the query is to code. That's why Nature gave us cut'n'paste.

The following query uses a couple of tricks. The Common Table Expression (aka Sub-Query Factoring) clause encapsulates the query on RegistrationDetail so we can easily refer to it in multiple places. The sub-query uses an Analytic function ROW_NUMBER() which allows us to identify each Detail record within the RegistrationID group. Both these features wwre introduced in Oracle 9i so they aren't new, but lots of people still don't know about them.

The main query uses Outer Joins to connect the Registration table multiple times to rows in the sub-query. It joins on RegistrationID and the derived DetNo.

SQL> with dets as
  2      ( select
  3              registrationid
  4              , owner
  5              , type
  6              , distance
  7              , detailid
  8              , row_number() over (partition by registrationid
  9                                   order by detailid) as detno
 10          from registrationdetail )
 11  select
 12      reg.registrationid
 13      , reg.somedate
 14      , reg.totlength
 15      , det1.detailid as detId1
 16      , det1.owner as owner1
 17      , det1.type as type1
 18      , det1.distance as distance1
 19      , det2.detailid as detId2
 20      , det2.owner as owner2
 21      , det2.type as type2
 22      , det2.distance as distance2
 23      , det3.detailid as detId3
 24      , det3.owner as owner3
 25      , det3.type as type3
 26      , det3.distance as distance3
 27  from registration reg
 28          left join dets det1 on ( reg.registrationid = det1.registrationid
 29                                   and det1.detno = 1 )
 30          left join dets det2 on ( reg.registrationid = det2.registrationid
 31                                   and det2.detno = 2 )
 32          left join dets det3 on ( reg.registrationid = det3.registrationid
 33                                   and det3.detno = 3 )
 34  order by reg.registrationid
 35  /
REGISTRATIONID SOMEDATE   TOTLENGTH     DETID1 OW TY  DISTANCE1     DETID2 OW TY  DISTANCE2     DETID3 OW TY  DISTANCE3
-------------- --------- ---------- ---------- -- -- ---------- ---------- -- -- ---------- ---------- -- -- ----------
             1 01-JAN-10          5          1 TD UB        1.5          2 AB US          2          3 TD UQ          4
             2 01-FEB-10         15          4 AB UQ         13          5 AB UR       13.1
             3 05-FEB-09         10          6 TD US          5

SQL>

Obviously if you have four Detail records per RegistrationID you will need four of those Outer Joins (and four sets of columns in the projection).

edit

I have just re-read your question and spotted the dread words "No maximum number". Sorry, in that case you're out of luck. The only way of solving this problem with a variable number of sets is with dynamic SQL, which you have effectively ruled out (because you would need to create additional schema objects).

edit 2

There is another solution, which is just about extracting the data and forgetting the layout. Oracle allows us to declare inline cursors, that is nested select statements, in the projection alongside scalars. This passes the problem of displaying the output to a client tool.

In this version I use Oracle's built-in XML functionality to produce the output (on the basis that lots of tools can render XML these days). The RegistrationDetails records are group within an XMLElement called REG_DETAILS which is nested within each Registration record.

with dets as
    ( select
            registrationid
            , owner
            , type
            , distance
            , detailid
            , row_number() over (partition by registrationid
                                 order by detailid) as detno
        from registrationdetail )
select
    xmlelement("AllRegistrations"
        , xmlagg(
            xmlelement("Registration"
               , xmlforest( reg.registrationid
                            , reg.somedate
                            , reg.totlength
                            , ( select xmlagg(
                                        xmlelement("RegDetail"
                                            , xmlforest(dets.detailid
                                                        , dets.owner
                                                        , dets.type
                                                        , dets.distance
                                                        , dets.detno
                                                        )
                                                    )
                                                )
                                from dets 
                                where reg.registrationid = dets.registrationid
                              ) as "RegDetails"
                          )
                      )
                 )
              )
from registration reg
order by reg.registrationid
/

Answer 4

You can't have multiple columns with the same name in the same query - oracle will rename them as 'Date_1', 'Date_2' etc. What is wrong with having several rows? How are you accessing it?