Search code examples
mysqlsqlmysql-5.6

Mysql query result generate months(1-12) for each category and arrange in same column order by category

I want to generate months no for 1-12 for each category. There are two categories, DEBIT and CREDIT. So, after finish print 1-12 for DEBIT. Then, it should go to next category and print the same with CREDIT in category column.

SELECT mon, @c:=@c+1 as cat_no, category
FROM
(
 SELECT @m:=@m+1 as mon FROM
(   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION 
  SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12)a,
 (SELECT @m:=0)c
)d,

(SELECT 'DEBIT' as category UNION SELECT 'CREDIT' as category)b,
(SELECT @c:=0)e

The fiddle here.

Result: result

The result show the column category display two categories for each month before go to next. But, I expected to output all 1-12 before go to next month.

Expected:

expected outcome

Thank you.

Solution 
  • SELECT mon.mon, cat.cat_no, cat.category
FROM ( SELECT 1 mon UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
       SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION 
       SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ) AS mon
CROSS JOIN ( SELECT 1 cat_no, 'DEBIT' category UNION
             SELECT 2, 'CREDIT' ) cat
ORDER BY cat_no, mon

    https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=60416e7875ba7eb886e804c0bddbcadb