join rows

[Janthine]

Hello, hope someone can help me out here

 

I have a table,

 

 

Table Scores

 

 

First I want to join the rows on ID when the score is the same, so that in the example below ID 2 has one row.

 

 

But is it also possible to remove the score column and put the score in the different year-columns as below?

 

Thank you

[vinny42]

The year1, year2, year3 columns smell like an error in the design, repeating columns are usually not as flexible as you might want.

 

Your first question could be a simple as running an aggregate:

SELECT id, SUM(year1), SUM(year2), ... FROM table GROUP BY id;

 

The second question requires more rules, you have put A and C in the same row, and all the B's in the second, why can A and C be merged, but the B in year-2 cannnot?

[Janthine]

Hello,

 

thanks Vinny, yes by SUM I get the score counted up.

 

The second one, I thought the Bs were merged into one row.

[Janthine]

Oh I want one row per ID

[Janthine]

I think it works by command group_concat ?

[vinny42]

Your second question is not group_concat, because year1 has A and B, but your example shows separate rows for year1, and the first row contains A and C, so there is something special about B, or you haven't thought enough about what you acrually want.

[Janthine]

No it is random, A B C can be placed anywhere

[Janthine]

if ID 1 has in year_1 a score of B and C I would like to see in column year_1: B,C

[Janthine]

The first table is an example how I worked it out..so per score there is a row now ...now I want to bring it back per ID and that worked with what you explained

 SELECT id, SUM(year1), SUM(year2), ... FROM table GROUP BY id

and when I write

SUM(year1)>0 year1

it gives the scores per ID in each column.

Now I like to see the different scores in one row but I guess it's won't work out

[fenway]

This is screaming for normalization, but this will work (link to http://sqlfiddle.com/#!2/30c2b/1/0):

select id
, IFNULL(group_concat(year1),0) as `Year 1`
, IFNULL(group_concat(year2),0) as `Year 2`
, IFNULL(group_concat(year3),0) as `Year 3`
from (
select id
, IF(year1,score,null) as year1
, IF(year2,score,null) as year2
, IF(year3,score,null) as year3
from scores
) t1
group by id;

Obviously, depending on how you want duplicates and sorting, you'll need to tweak the group_concat() call slightly.

Edited September 8, 2013 by fenway
sqlfiddle