Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 6 down vote favorite
2

Hi I have a two tables

Student
--------
Id  Name
1   John    
2   David
3   Will

Grade
---------
Student_id  Mark
1           A
2           B
2           B+
3           C
3           A

Is it possible to make native Postgresql select to get results like this:

Name    Array of marks
-----------------------
'John',     {'A'}
'David',    {'B','B+'}
'Will',     {'C','A'}

But not like this

Name    Mark
----------------
'John',     'A'
'David',    'B'
'David',    'B+'
'Will',     'C'
'Will',     'A'
share|improve this question
add comment

3 Answers

up vote 11 down vote accepted

Use array_agg: http://www.sqlfiddle.com/#!1/5099e/1

SELECT s.name,  array_agg(g.Mark) as marks        
FROM student s
LEFT JOIN Grade g ON g.Student_id = s.Id
GROUP BY s.Id

By the way, if you are using Postgres 9.1, you don't need to repeat the columns on SELECT to GROUP BY, e.g. you don't need to repeat the student name on GROUP BY. You can merely GROUP BY on primary key. If you remove the primary key on student, you need to repeat the student name on GROUP BY.

CREATE TABLE grade
    (Student_id int, Mark varchar(2));

INSERT INTO grade
    (Student_id, Mark)
VALUES
    (1, 'A'),
    (2, 'B'),
    (2, 'B+'),
    (3, 'C'),
    (3, 'A');


CREATE TABLE student
    (Id int primary key, Name varchar(5));

INSERT INTO student
    (Id, Name)
VALUES
    (1, 'John'),
    (2, 'David'),
    (3, 'Will');
share|improve this answer
 
Oh my god thank you so much about your remark about select/group, that is so awesome! That was really annoying! –  mrbrdo Sep 25 at 10:28
add comment
up vote 3 down vote

What I understand you can do something like this:

SELECT p.p_name, 
    STRING_AGG(Grade.Mark, ',' ORDER BY Grade.Mark) As marks
FROM Student
LEFT JOIN Grade ON Grade.Student_id = Student.Id
GROUP BY Student.Name;

EDIT

I am not sure. But maybe something like this then:

SELECT p.p_name, 
    array_to_string(ARRAY_AGG(Grade.Mark),';') As marks
FROM Student
LEFT JOIN Grade ON Grade.Student_id = Student.Id
GROUP BY Student.Name;

Reference here

share|improve this answer
 
I think he wants a pgsql array, not a comma-separated string –  ThiefMaster♦ Jun 7 '12 at 8:35
 
Update the answer –  Arion Jun 7 '12 at 8:41
add comment
up vote 2 down vote

You could use the following:

SELECT Student.Name as Name,
       (SELECT array(SELECT Mark FROM Grade WHERE Grade.Student_id = Student.Id))
       AS ArrayOfMarks 
FROM Student

As described here: http://www.mkyong.com/database/convert-subquery-result-to-array/

share|improve this answer
2  
Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. –  ThiefMaster♦ Jun 7 '12 at 8:31
 
Thank you, i've updated my answer. I hope this is better? –  niko Jun 7 '12 at 8:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.