CODEWITHSUNDEEP
oracle-databasejoin
Himanshu sharma
Himanshu sharma

Reputation: 7881

How to combine multiple row data in a column in select query oracle?

For example:- I have 3 tables .

student :

student_id | name | rollNo | class 
         1 |  a1  | 12     | 5
         2 |  b1  | 11     | 5

address: there can be multiple address for a user

street  | district| country |student_id
  gali1 | nanit   | india | 1
  gali2 | nanital | india | 1

Books : There can be muliple book for the user

       book   | book_id |student_id
      history | 111      | 1
      Science | 112    | 1

This is example . I want data to be like this in output . If i select for student_id 1. Then this would be result

student_id | name | rollNo | class | addresslist   | booklist
          1 |  a1  | 12     |  5    | some sort of |  some sort of
                                    |  list which  |  list which
                                    |  contain both|  contain both
                                    |  the address |  the book detail
                                    |  of user     |  of user

I am using 12.1 which does not support json for now it is in 12.2 .

addresslist can be like this you can create list as you want but it should have all this data.

[{street:"gali1","distict":"nanital","country":"india","student_id":1},{"street":"gali2","distict":"nanital","country":"india","student_id":1}]

same for booklist

Thanks in advance .

Upvotes: 0

Views: 47

Answers (1)

MT0
MT0

Reputation: 167774

Something like:

WITH json_addresses ( address, student_id ) AS (
  SELECT '[' ||
         LISTAGG(
           '{"street":"' || street || '",'
             || '"district":" || district || '",'
             || '"country":" || country|| '"}',
           ','
         ) WITHIN GROUP ( ORDER BY country, district, street )
         || ']',
         student_id
  FROM   address
  GROUP BY student_id
),
json_books ( book, student_id ) AS (
  SELECT '[' ||
         LISTAGG(
           '{"book_id":"' || book_id || '",'
             || '"book":" || book || '"}',
           ','
         ) WITHIN GROUP ( ORDER BY book, book_id )
         || ']',
         student_id
  FROM   book
  GROUP BY student_id
)
SELECT s.*, a.address, b.book
FROM   student s
       INNER JOIN json_addresses a
       ON ( s.student_id = a.student_id )
       INNER JOIN json_books b
       ON ( s.student_id = b.student_id );

Upvotes: 1

Related Questions