How to somehow do a loop within select?

Question

I have this table.

+------+-------+--------+
| CODE | MONTH | AMOUNT |
+------+-------+--------+
|    2 |     1 |    100 |
|    3 |     1 |    200 |
|    2 |     2 |    300 |
|    3 |     2 |    400 |
+------+-------+--------+

And, this is the result that I'm trying to get.

+------+---------+---------+
| CODE |     MO1 |     MO2 |
+------+---------+---------+
|    2 |     100 |     300 |
|    3 |     200 |     400 |
+------+---------+---------+

I know that looping within select isn't possible.
I imagine I could maybe use CASE but I don't have a definite count of months as in my example above wherein there are only data for the months of January and February. I would have data for March later on. How can I do this?

Is there a maximum of 12 months or number of months is really unknown? — Tomas Greif, Apr 19 '13 at 07:41

Himanshu · Answer 1 · 2013-04-30T13:19:07.170

5

Since you don't know the definite count of months you can do it dynamically like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when Month = ''',
      Month,
      ''' then Amount else 0 end) AS `Mo',
      Month, '`'
    )
  ) INTO @sql
FROM Table2;


SET @sql = CONCAT('SELECT Code, ', @sql, '
                  FROM Table2 
                  GROUP BY Code');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Result:

| CODE | MO1 | MO2 |
--------------------
|    2 | 100 | 300 |
|    3 | 200 | 400 |

See this SQLFiddle

edited Apr 30 '13 at 13:19

answered Apr 19 '13 at 06:56

Himanshu

31,810
31
111
133

Thank you. I'm wondering though what those 3 single quotation marks mean. Phpmyadmin returns a syntax error near 'SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(case when Month = ''', '. – esandrkwn Apr 19 '13 at 07:18
@esandrkwn - Now see the updated answer. That was for `varchar` datatype while here month is not `varchar`. So removed it. – Himanshu Apr 19 '13 at 07:23
Thanks a lot I appreciate it very much. I'm not sure what's going on but I copied the codes exactly from fiddle after creating that same table Table2. When the delimiter is set as ';;' or without a delimiter I get the same error as above. When I set it as ';' I get this error: '#1243 - Unknown prepared statement handler (stmt) given to EXECUTE'. Any idea? – esandrkwn Apr 19 '13 at 07:53
@esandrkwn - Same issue is [here](http://stackoverflow.com/questions/12270953/combine-multiple-rows-into-one-mysql#comment-16458111). You need to do it with separate queries. One to build the `stmt` and one do execute it. [See the comment of the OP here](http://stackoverflow.com/questions/12270953/combine-multiple-rows-into-one-mysql#comment-16660514) – Himanshu Apr 19 '13 at 08:07
@esandrkwn - And please let me know once your do it successfully. – Himanshu Apr 19 '13 at 08:21
@esandrkwn - Hi I have edited my answer. Please check if it works for you now or not. – Himanshu Apr 30 '13 at 13:19

score 0 · Answer 2 · answered Apr 19 '13 at 07:05

0

SELECT A.CODE AS CODE, A.AMOUNT as MO1, B.AMOUNT as MO2 FROM
((SELECT CODE, AMOUNT from Table1 WHERE MONTH=1) AS A
INNER JOIN
(SELECT CODE, AMOUNT from Table1 WHERE MONTH=2) AS B
ON
A.CODE=B.CODE)

Add more inner joins if more than 2 months

Hope this helps :)

answered Apr 19 '13 at 07:05

faisal

1,327
10
19

The OP already told he don't know the definite number of Months. So this won't help him. – Himanshu Apr 19 '13 at 07:09
Assuming number of months will not exceed 12, this sql still works. But, your sql seems smarter :) – faisal Apr 19 '13 at 07:12

How to somehow do a loop within select?

2 Answers2

See this SQLFiddle