2

I have data like 

id  var1 var2
1   A1   B1
1   A2   B2
1   A3   B3

How can I get the data like 

id v1 v2 v3 v4 v5 v6
1 A1 B1 A2 B2 A3 B3

Thanks in advance.

Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
himanshu
  • 29
  • 2

1 Answers1

0

Given that you may potentially need more columns depending on how many records occur for each id group, I would suggest an alternative approach using group concatenation:

SELECT
    id,
    GROUP_CONCAT(CONCAT_WS(',', var1, var2)) AS output
FROM yourTable
GROUP BY
    id;

enter image description here

Demo

If you have more columns to add, you only would need to add additional columns to CONCAT_WS.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    OP want's it in seperate column like v1, v2 .. not in one column – Fahmi Oct 11 '18 at 05:38
  • 1
    @fa06 It would be a lot of work to write an exact query for that, because we either need to use `ROW_NUMBER`, or, more likely, dynamic SQL. Especially if the number of rows per group is variable. Using `GROUP_CONCAT` is an alternative which is much easier IMHO. – Tim Biegeleisen Oct 11 '18 at 05:41
  • 1
    yes you are right it is a pivot problem and in mysql pivoting is a tough one to write, however I didn't give you downvote – Fahmi Oct 11 '18 at 05:43
  • 1
    i don't know why this answer get downvoted.. – dwir182 Oct 11 '18 at 06:16
  • @dwir182 I fumbled around for a bit, and the downvoter may have seen what he perceived to be an incorrect answer at the time. – Tim Biegeleisen Oct 11 '18 at 06:23
  • @dwir182 may b op given him down vote cause this answer is not covered his problem solution – Zaynul Abadin Tuhin Oct 11 '18 at 07:07