1

https://i.stack.imgur.com/5Pw2L.png

I have a problem that I would like to solve for pre-processing in MySQL. I have a select that returns multiple rows and columns for an id. I would like to transform the rows into columns for the same id, grouping them as per the attached figure. The column names are not important to me because I only need the values for each id.

+---+---+---+---+-----+---+
| 1 | a | b | c | ... | x |
+---+---+---+---+-----+---+
| 1 | d | e | f | ... | y |
+---+---+---+---+-----+---+
| 2 | g | h | i | ... | z |
+---+---+---+---+-----+---+
| 2 | j | k | l | ... | q |
+---+---+---+---+-----+---+
| 3 | m | n | o | ... | w |
+---+---+---+---+-----+---+
| 3 | p | q | r | ... | t |
+---+---+---+---+-----+---+

+---+---+---+---+-----+---+---+---+---+-----+---+
| 1 | a | b | c | ... | x | d | e | f | ... | y |
+---+---+---+---+-----+---+---+---+---+-----+---+
| 2 | g | h | i | ... | z | j | k | l | ... | q |
+---+---+---+---+-----+---+---+---+---+-----+---+
| 3 | m | n | o | ... | w | p | q | r | ... | t |
+---+---+---+---+-----+---+---+---+---+-----+---+
Shadow
  • 33,525
  • 10
  • 51
  • 64
Orlando Stein Jr
  • 81
  • 4
  • If MySQL isn't a blocker, you should be able to get this functionality using PostgreSQL – Blue Jun 28 '17 at 00:16
  • This transformation is called pivoting and has been asked and answered here on SO several times. The linked duplicate topic explains both static and dynamic pivoting. In this particular case you will probably need to use min or max as an aggregate function instead of counts. Pls note that it may be more efficient to perform such a transformation in the application code than in sql. – Shadow Jun 28 '17 at 01:03

1 Answers1

1

Unfortunately there is no way to create columns like that on the fly, if you have a variable amount of id repetitions in a table. You could use group concat to get the same columns into one comma separated column

Select Id, Group_Concat(Col1) As Col1, 
        Group_Concat(Col2) As Col2, 
        Group_Concat(Col3) As Col3, ... 
        Group_Concat(Coln) As Coln 
From table 
Group By Id
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Patrick Di Martino
  • 102
  • 4