0

I have tables with information similar to the following:

Table A is a list of circuits:

Circuit  |  CktType  |  CktSize
--------------------------------
CKT1     |  ABC123   |  10
CKT2     |  ABC123   |  12
CKT3     |  XYZ789   |  10

Table B is a list of Raceway:

Raceway  |  RwyType  |  RwySize
--------------------------------
RWY1     |  C        |  4
RWY2     |  T        |  4x6
RWY3     |  T        |  8x12

Table C is a list of how the circuits go through the Raceway:

Circuit  |  Sequence |  Raceway
--------------------------------
CKT1     |  1        |  RWY1
CKT1     |  2        |  RWY2
CKT1     |  3        |  RWY3
CKT2     |  1        |  RWY2

Table C may or may not have entries for all items in tables A and B. There is not a set number or a maximum number of entries in table C for each item in tables A and B.

I would like to write 2 queries in Oracle to retrieve the following data (clearly the queries would be very similar so only really looking for help writing one of them).

All Circuit information with the raceways the circuit goes through Results Desired:

Circuit  |  CktType  |  CktSize   |  Raceway
----------------------------------------------
CKT1     |  ABC123   |  10        | RWY1, RWY2, RWY3
CKT2     |  ABC123   |  12        | RWY2
CKT3     |  XYZ789   |  10        | (null)

All Raceway information with the circuits in the raceway: Results Desired:

Raceway  |  RwyType  |  RwySize  |  Circuit
----------------------------------------------
RWY1     |  C        |  4        | CKT1
RWY2     |  T        |  4x6      | CKT1, CKT2
RWY3     |  T        |  8x12     | CKT1

Thanks in advance.

David McCarthy
  • 1

2 Answers2

1

This would be one of your two queries. That produces each circuit information and against it the Raceway sequence separated by commas.. check it out.

  SELECT Circuit,
         CktType,
         CktSize,
         RTRIM (
            XMLAGG (XMLELEMENT (e, Raceway || ', ') ORDER BY Sequence).EXTRACT (
               '//text()'),
            ', ')
            Raceways
    FROM (SELECT t_A.Circuit,
                 t_A.CktType,
                 t_A.CktSize,
                 t_C.Raceway,
                 t_c.Sequence
            FROM    tableA t_A
                 LEFT OUTER JOIN
                    tableC t_C
                 ON t_A.Circuit = t_C.Circuit)
GROUP BY Circuit;
user2275460
  • 339
  • 3
  • 14
0

EDIT: After re-reading your post, I realized this would not work for you. Try the "For XML PATH". Here is a great example: sql-query-concatenating-results-into-one-string

Community
  • 1
  • 1
Steve
  • 5,585
  • 2
  • 18
  • 32