kings kings - 1 month ago 6
SQL Question

Retrieve data in a single row using group keyword in sql

Let's say I have a table tbl_marks with columns and data as below.

name id section1 section2 section3 section4 year
cherry 1 100 101 102 103 2016
cherry 1 200 201 202 203 2015
cherry 1 300 301 302 303 2014


Expected Output Format :

cherry 1 100101102103 200201202203 300301302303


I would like to have scores of all sections of one year to be concatenated and then followed by scores of another year separated by space.
So I need 5 columns in single row. (name, id, scores of year1, scores of year2, scores of year3)

Please let me know how should i update the query below. Thank you.

Query : select name, id, ??? from tbl_marks group by id;

Answer

Use GROUP_CONCAT:

SELECT name,
       id,
       GROUP_CONCAT(CONCAT(section1, section2, section3, section4)
                    ORDER BY section1 SEPARATOR ' ')
FROM yourTable
GROUP BY name, id

This answer assumes that you want three columns in your result set, the three columns being the name, id, and a CSV list of marks for that name/id group (123,456,789 in this case).

SQLFiddle

(courtesy of @Luke)