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

Question

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 .

Group by student_id and use listagg and string concatenation || to get the string of addresses and books. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 10, 2017 at 8:02
If it's just about a reporting query, then after you create it, or put it in a view, you can select the addresses with the listagg() built-in function, smth like this select s.student_id, s.name, s.rollno, s.class , listagg(a.country || ' ' || a.district || ' ' || a.street) within group (order by a.country) as addresslist , listagg(b.book_id || ' ' || b.book) within group (order by a.book_id) as booklist from student s , address a, books b where s.student_id = a.student_id and s.student_id = b.student_id; group by could be added as well. — g00dy
– g00dy, Commented Oct 10, 2017 at 8:05
@g00dy This gives error ORA-00937: not a single-group group function — Himanshu sharma
– Himanshu sharma, Commented Oct 10, 2017 at 8:27
we have to add group by at the end too. group by s.student_id, s.name, s.rollno, s.class; — Himanshu sharma
– Himanshu sharma, Commented Oct 10, 2017 at 11:56
A common mistake is missing fields in the last group by clause. Every select field should be also in the group by clause. — Himanshu sharma
– Himanshu sharma, Commented Oct 10, 2017 at 12:04

MT0 · Accepted Answer · 2017-10-10 08:24:45Z

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 );

@Himanshusharma It is done with a single query. If you mean "can I join the tables and then use LISTAGG"? Not if you do not want duplicates in your JSON.
that would be help for me and for all who are searching solution.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related