I want my subquery results (multi rows) return as a singale row in my query. For example.
select u.url, (select c.categoryid from category c
where c.categoryid = u.categoryid) from url u
If url contains multiple categories, subquery will return multiple rows as following
P.S Subquery return multiple rows is not supported in most of the database but PostgreSQL
url | categoryid
---------------------
1 | 1
1 | 2
2 | 1
Result i wanted is as following
url | categoryid
---------------------
1 | 1,2
2 | 1
I’m not sure how easy it can achieve in others database like oracle or mysql, however PostgreSQL provided Array data type, using array it’s very easy to get what i want. I have to changed my query to
select u.url, (select array(select c.categoryid from category c
where c.categoryid = u.categoryid))as categoryid from url u
Result ~
url | categoryid
---------------------
1 | {1,2}
2 | 1
Done ~ PostgreSQL Array Rock ~
SELECT u.url, array_agg(u.categoryid) AS categoryids
FROM url u
ORDER BY url
thanks for the informative blog entries.
I have the following problem and would appreciate your comments.
I am trying to port the following Oracle sql to postgres:
SELECT CAST (MULTISET (SELECT cd.a, cd.xml_key, cd.b,
cd.c, cd.d, cd.e
FROM table1 cd
WHERE cd.a = t.a
) AS custom_typ )AS my_list
FROM table2 t, table3 aclp
WHERE t.foo = aclp.bar
here the subquery has “multiple columns (not just one as in your example”. How could I convert the result set from the inner query into an array (of row set)?
Sorry, Forgot to check email notification
Hey there, does anyone know how can I read this output (an array column) using hibernate ? I’ve done this using array_to_string and then splitting the string column in Java, but I don’t like that. The any on SQLQuery was deprecated. custom and Hibernate.* types are deprecated also.
If you want the result 1,2 you can get it by using the array_to_string function
Like this :
SELECT u.url, (SELECT array_to_string(array(SELECT c.categoryid FROM category c
WHERE c.categoryid = u.categoryid)), ‘,’) AS categoryid FROM url u
Thanks a lot! I was wondering how to do this for one year!
Thanks to PostgreSQL ~