SQL split values on multiple rows

user_15648245 0
Viewed 65

I made a select and it looks like this

SELECT name,class_name
FROM students
INNER JOIN classes on classes.id = students.id

and I get a table like

| name     | class_name     |
| -------- | -------------- |
| Daniel   | Math           |
| Johnny   | Physics        |
| Johnny   | Math           |
| Andrew   | English        |
...

How am I supposed to split the table to get the first two classes each student attends (students can attend more than two classes or a single class)

Example:

| name     | class_1        | class_2  |
| -------- | -------------- | ---------|
| Daniel   | Math           |          |
| Johnny   | Math           | Physics  |
| Andrew   | English        |          |
...

I was thinking of transposing the table, however, I don't know how to actually do it or if it's a good approach.

sql
1 Answers

I concatenated every different class for each student in a single column with commas as separators (because classes can contain spaces, for example, "Computer Science") then I split the single column into the two required classes.

WITH part
     AS (SELECT NAME,
                class_name
         FROM   students
                INNER JOIN classes
                        ON classes.id = students.id),
     ans
     AS (SELECT NAME,
                Array_to_string(Array_agg(class_name), ',') AS cl
         FROM   part
         GROUP  BY NAME)
SELECT NAME,
       Split_part(cl, ',', 1) AS class_1,
       Split_part(cl, ',', 2) AS class_2
FROM   ans
user_156482450
Related