Search code examples
sqlqgis

transform table with duplicate

I'm trying to transform a base with duplicates into a new base according to the attached model impossible without duplicate I don't see how I can do in advance thank you for your help

original base

IDu| ID | Information
1 |A |1
2 |A |2
3 |A |3
4 |A |4
5 |A |5
6 |B |1
7 |B |2
8 |B |3
9 |B |4
10 |C |1
11 |D |1
12 |D |2
13 |D |3

base to reach

  • ID | Resultat/table2 | plus grand valeur
    A |(1,2,3,4,5) |5
    B |(1,2,3,4) |4
    C |(1) |1
    D |(1,2,3) |3
Solution

  • You can use GROUP_CONCAT (https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php):

    SELECT
  ID, GROUP_CONCAT(INFORMATION), COUNT(INFORMATION)
FROM
  TABLE
GROUP BY
   ID