I have this query
select ts.nome,
year(d.data_recebimento) as ano,
count(*) as qtd
from documento d inner join documento_tipo_solicitacao dts on (d.id = dts.documento_id)
inner join tipo_solicitacao ts on (dts.tipo_solicitacao_id = ts.id)
where year(d.data_recebimento) in (2008, 2010)
and ts.id in (245, 671, 210)
group by ts.nome,
year(d.data_recebimento)
order by 1, 2
It returns a table like this:
|---------|---------|------|
|nome |ano |qtd |
|---------|---------|------|
|AAAA |2008 |10 |
|AAAA |2010 |15 |
|BBBB |2008 |20 |
|CCCC |2008 |12 |
|CCCC |2010 |13 |
|---------|---------|------|
The code from arguments are dynamics, ex: year(d.data_recebimento) could be any year an ts.id in (245, 671, 210) coulb be any code send by the user. Here AAAA = 210, BBBB = 245 and C = 671.
I would like to write a query that return a table like below:
|---------|---------|------|
|nome |2008 |2010 |
|---------|---------|------|
|AAAA |10 |15 |
|BBBB |20 |0 | --> BBBB on 2010 could be blank.
|CCCC |12 |13 |
|---------|---------|------|
Thanks.
You need some kind of dynamic sql.
In MySql it can be done using Prepared Statements
Below is a simple example
- assumming that results of your query is saved into a temporary table named your_query_goes_here
SET @sql = (
SELECT concat( 'SELECT `nome`, ',
( SELECT group_concat( DISTINCT
concat('min(if( `ano`=', ano,
',`qtd`,null)) AS col_',`ano`) )
FROM your_query_goes_here
),
' FROM your_query_goes_here GROUP BY `nome`')
)
;
SELECT @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;