mysql group_concat output as the another query column list

Question

I want to generate a cross tab query in MySQL. I used group_concat, but it is not working. I issued the following query to generate the year list:

set @v1 = (SELECT GROUP_CONCAT(DISTINCT 
CONCAT('\\nsum(CASE WHEN myear=\"', myear ,'\" 
THEN amount ELSE NULL END) AS\"', myear,'\"')) AS column_list
FROM mdata
where myear > 1972 and myear < 1974);
select myear, amount, @v1 from mdata;

It produced the list of years in @v1 but with binary field. I want to use it in my next query to make the cross tab query.

I can run the first query with PHP and store the value in a variable and use it in the next query, but how it can be done in MySQL?

please show your tables and expected output. :) – bonCodigo Dec 01 '12 at 07:27 — bonCodigo, Dec 01 '12 at 07:27

score 1 · Answer 1 · answered Dec 01 '12 at 08:31

1

You have to prepare a statement from the SQL to be executed:

SET @v1 = (
  SELECT CONCAT('
    SELECT myear, amount, ',
           GROUP_CONCAT(DISTINCT CONCAT(
             'SUM(CASE WHEN myear=', QUOTE(myear), ' THEN amount END)
                AS `', REPLACE(myear, '`', '``'), '`'
           )), '
    FROM mdata'
  )
  FROM   mdata
  WHERE  myear > 1972 AND myear < 1974
);

PREPARE stmt FROM @v1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Dec 01 '12 at 08:31

eggyal

122,705
18
212
237

above solution not working given error "Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE" second i tried to set variable and then issue select $v1 its return null. my data like this.. my table structure acode,amount,year i just want to show the cross tab data like acode 1972 1973 1972....under the year heading show amount.. where clause to restict the year will not be applied show as much year as table have – user1480032 Dec 01 '12 at 10:15
@user1480032: Like [this](http://sqlfiddle.com/#!2/8fe04/1/2)? Although one wonders why, in this case, you don't simply do [this](http://sqlfiddle.com/#!2/8fe04/2/0)? – eggyal Dec 01 '12 at 10:44
I think u misunderstand my request.. i have the table like you mention i want to display in cross tab report on row acode and each column for each year amount its mean if i have data of 10 years then it will display 10 column for each year... – user1480032 Dec 01 '12 at 12:25
sorry eggyal i did not see your first link this... i think it is the solution i need thks – user1480032 Dec 01 '12 at 12:36
I check it return no result then i separate check the group_concat it return blog fields. Then i try to run the query and print the value of @v1 it return null...i have the data in the file. i knew this the solution but i missed something...Second i try to add accode in your code on fiddle but it give the error "Operand should contain 1 column(s)" – user1480032 Dec 01 '12 at 13:29
Thanks for solution. It is working with command prompt MySQL client. but it give me the error when i run in MySQL query browser...i do not figure it out. – user1480032 Dec 03 '12 at 10:02
I USED MYSQL WORKBENCH AND IT IS WORKING PERFECT – user1480032 Dec 03 '12 at 12:59

mysql group_concat output as the another query column list

1 Answers1