Auto generate MYSQL columns from "where" condition

Question

I have the following query to generate column in a select query, which I manually type, however I'd like to have the columns generated through an iterative loop which takes the start date and end date and then outputs the corresponding years between the start date and end date

    SELECT 
        SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2011",1,0))"2011"
        ,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2012",1,0))"2012"
        ,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2013",1,0))"2013"
        ,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2014",1,0))"2014"
        ,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2015",1,0))"2015"
        ,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2016",1,0))"2016"
        ,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2017",1,0))"2017"
        ,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2018",1,0))"2018"
        FROM  year_table a 
WHERE DATE_FORMAT(Date_First_Seen,"%Y") BETWEEN "2011" AND "2018"

SQL queries do not support dynamic columns; not in name nor in count. You could do `COUNT` with a `GROUP BY YEAR(a.Date_First_Seen)` to get _rows_ with the data you are looking for though. — Uueerdo, Jul 12 '18 at 18:20
`DATE_FORMAT(a.Date_First_Seen,"%Y")` can be replaced with `YEAR(a.Date_First_Seen)`. — Schwern, Jul 12 '18 at 18:23
Thanks @Uueerdo but I was looking to format the query in a columnar format,other row type solutions I have already done, but thanks again so your acknowledgement — dames, Jul 12 '18 at 18:57
The best you can hope for getting those kinds of results from MySQL is a stored procedure that first runs a query to figure out the number of columns will be needed; and then uses that information to dynamically construct a query that will get those results. (and dump them into a temp table it can select from, as dynamically prepared and executed SELECT queries' results do not normally come back automatically from stored procs iirc). — Uueerdo, Jul 12 '18 at 19:08
This is called a pivot table query. In SQL, such cases of pivot table queries require you to code the columns explicitly. You could run a preliminary query to build a query dynamically based on the distinct year values found in the data. — Bill Karwin, Jul 12 '18 at 19:08
But it's simpler to fetch the results in rows, not columns, as shown in the answer from @Schwern. You can write application code to collect the multi-row result and display in whatever layout you want. — Bill Karwin, Jul 12 '18 at 19:11

Schwern · Answer 1 · 2018-07-12T19:53:59.923

This query can be significantly reduced and the redundancies eliminated. Use year to get the year of a date with less fuss. Then group by year and count the dates in each group.

select
    year(date_first_seen) as year_first_seen,
    count(date_first_seen)
from year_table
where year_first_seen between 2011 and 2018
group by year_first_seen

If you want it as a single column, use a pivot table. Here's an answer about how to accomplish that in MySQL.

Auto generate MYSQL columns from "where" condition

1 Answers1