0

I am trying to transform some rows into columns in MySQL. I know it has been asked and answered previously, like here.

My problem is, there is nothing in my rows on which I can apply the 'if' construct. (At least I think so.) E.g. For the following input, 

2  5  1000
2  6  2000

I can run this query:

INSERT INTO SUMMARY
  (user_id,valueA,valueB) 
  SELECT d.user_id,
         MAX(CASE WHEN d.code = 5 THEN d.value ELSE NULL END),
         MAX(CASE WHEN d.code = 6 THEN d.value ELSE NULL END),
    FROM DETAILS d
GROUP BY d.user_id

and get this output:

2  1000  2000

But my problem is, my input is something like this:

2  6  1000
2  6  2000

(The values in the second column are not unique.) And i still need the same output, i.e.:

2  1000  2000

Can it be done in MySQL? If yes, can anyone help me with this?

Community
  • 1
  • 1
Bhushan
  • 18,329
  • 31
  • 104
  • 137
  • Could you please provide a DDL script of your table? Do you know how many "values" there will be for a given "user_id", i.e. how many columns in your final resultset? Or is it a random number? In your example, how do you know 1000 must be in the first column and 2000 in the second? – Bruno Gautier Jul 12 '11 at 14:41
  • well, i am not sure about that, but for now we can assume that there will be max 10 rows for each user_id. – Bhushan Jul 12 '11 at 14:47

1 Answers1

1

Well, if you don't have any idea of how many columns there will be in your pivot table, nor have a value to decide which value should go in a given column, the best solution I can recommend is to use a GROUP_CONCAT function and then do some parsing in your code:

SELECT d.user_id, GROUP_CONCAT(
    d.value SEPARATOR ','
  ) AS val
FROM details d
GROUP BY d.user_id;
Bruno Gautier
  • 646
  • 4
  • 7