How to write such complex relations in SQL?

Question

if there is a table of r (c,s) and such relations (c means class, s means students), I want to know all possible combination of c1, c2, ..c5, s1,...s8 , How to write the SQL clause? I tried to write it using case when, but it's too slow. I'm using PostgreSQl.

c1:s1,s5,s7
c2:s1,s2,s3,s4
c3:s2,s6,s8
c4:s4,s5,s6
c5:s3,s7,s8

Normalize your database and you won't have problems like this. If you don't know what that means, I've heard good things about the book, Database Design for Mere Mortals. — Dan Bracuk, Apr 11 '14 at 13:55
possible duplicate of [how to make array\_agg() work like group\_concat() from mySQL](http://stackoverflow.com/questions/4054559/how-to-make-array-agg-work-like-group-concat-from-mysql) — Prahalad Gaggar, Apr 11 '14 at 13:59
no, this is different from the post of array_agg(). In my case, I know the relations of (c1:s1,s5,s7 ...) and all data of r (c,s). I want to figure out all possible combinations of c1,c2...s1,s2..s8. This is reverse application to the post of array_agg(). In my example, I need to use a serial of predicates under WHERE to represent the (c1:s1,s5,s7 ..) relation , but do not know how to. — user3419945, Apr 11 '14 at 14:10
even for CROSS JOIN, it's also hard to use SQL to represent such a relation....do you have some idea? @Mihai — user3419945, Apr 11 '14 at 14:36
It's hard because you are storing comma separated values, i.e. lists of students and not students in the `s` column. — ypercubeᵀᴹ, Apr 11 '14 at 14:37
Could you please say more about fiddle? I don't quite understand it ...@Mihai — user3419945, Apr 11 '14 at 14:40
If you post the `CASE` `WHEN` query that was too slow, that will help us figure out exactly what you're asking. — Dave Gray, Apr 14 '14 at 18:46

score 0 · Answer 1 · answered Apr 11 '14 at 14:34

0

Try this:

SELECT c, string_agg(s, ',')
FROM r
GROUP BY c;

Note: string_agg() was introduced in PostgreSQL 9.0.

answered Apr 11 '14 at 14:34

Racil Hilan

24,690
13
50
55

Thanks, but this is not my problem, please see above. – user3419945 Apr 11 '14 at 14:41

score 0 · Answer 2 · answered Apr 11 '14 at 14:52

Are you want to exchange the rows to table r(c,s) columns? If you want, You can use This SQL for exp :

digoal=# select split_part(val,':',1), regexp_split_to_table(split_part(val,':',2), ',') from (values ('c1:s1,s5,s7'),('c2:s1,s2,s3,s4')) as t(val);
 split_part | regexp_split_to_table 
------------+-----------------------
 c1         | s1
 c1         | s5
 c1         | s7
 c2         | s1
 c2         | s2
 c2         | s3
 c2         | s4
(7 rows)

How to write such complex relations in SQL?

2 Answers2