SQL regroup by column values into row

Question

I have a software that gives me data that look like those.

| Date |   Programme    | Groupe | Rat | Actif | RFS | Inactif |  Relevance  |   Etape  | 
|120227| ANS-FR2-85s TO |   5    |  9  |  20   |  5  |   10    | Rats finaux | Training |
|120228| ANS-FR2-85s TO |   5    |  9  |  10   |  3  |    4    | Rats finaux | Training |
|120229| ANS-FR2-85s TO |   5    |  9  |  100  | 20  |   50    | Rats finaux |    ShA   |
|120230| ANS-FR2-85s TO |   5    |  9  |  100  | 20  |   50    |    Exclus   | Training |
|120231| ANS-FR2-85s TO |   5    |  9  |  100  | 20  |   50    | Rats finaux | Training |
|120227| ANS-FR2-85s TO |   5    | 10  |  20   |  5  |   10    | Rats finaux | Training |
|120228| ANS-FR2-85s TO |   5    | 10  |  10   |  3  |    4    | Rats finaux | Training |
|120229| ANS-FR2-85s TO |   5    | 10  |  100  | 20  |   50    | Rats finaux |    ShA   |
|120230| ANS-FR2-85s TO |   5    | 10  |  100  | 20  |   50    |    Exclus   | Training |

I would like to be able to create a view that will regroup the 'RFS' according to the 'Rat' values and applying criteria for 'Etape' and 'Relevance'

Criteria

WHERE Relevance='Rats finaux'
WHERE Etape='Training'

It also is important to notice that the number of row is variable. So I could have like in this exemple 2 or 3 rows but also up to 20 rows. I think that maybe a loop might be great rather than calling each specific lines.

So it would look like this:

| Groupe | Rat | D1  | D2  |  D3  |
|   5    |  9  |  5  |  3  |  20  |
|   5    | 10  |  20 | 10  |      |

Thanks a lot

You can do something similar with group_concat(). All the resulting values would be character strings in one column, though. — Gordon Linoff, Jul 24 '12 at 17:25
might want to look into pivot...? http://stackoverflow.com/questions/7674786/mysql-pivot-table — gloomy.penguin, Jul 24 '12 at 17:39

score 0 · Accepted Answer · answered Jul 24 '12 at 17:51

SO usable data becomes (after where conditions):

 Date      Programme         Groupe      Rat     Actif   RFS     Inactif      Relevance        Etape  
120227   ANS-FR2-85s TO        5          9       20      5        10        Rats finaux     Training 
120228   ANS-FR2-85s TO        5          9       10      3         4        Rats finaux     Training 
120231   ANS-FR2-85s TO        5          9       100    20        50        Rats finaux     Training 

120227   ANS-FR2-85s TO        5         10       20      5        10        Rats finaux     Training 
120228   ANS-FR2-85s TO        5         10       10      3         4        Rats finaux     Training

How are we making the columns D1, D2, D3...? I see that for Group 5, Rat 9, you use the RFS values for D1, D2, D3... But for Group 5, RAT 10, you use the Actif values for the row data..? How and why is that done? Ultimately you probably need some kind of pivot table (which Mysql does not have) and probably, maybe, dynamic columns. I don't know. Let's try and understand your data needs first...

another link: http://www.jasny.net/articles/creating-a-cross-tab-in-mysql/

is that something you want? i could help more but i don't understand how you're getting the end result you provided as an example.

SQL regroup by column values into row

1 Answers1