How to get the particular value from the GROUP

I'm trying in MYSQL, please suggest the expected result

Thank you in advance

table 1

year	Cars
2000	BMW
2000	Maruti Suzuki
2000	TOYOTA
2001	Volkswagen
2001	Daimler
2001	Chevrolet

OUTPUT

Year	Cars
2000	BMW, Maruti Suzuki, TOYOTA
2001	Volkswagen, Daimler, Chevrolet

EXPECTING THE BELOW OUTPUT

Year	Car Model	TOYOTA	FORD
2000	BMW, Maruti Suzuki, TOYOTA	YES	NO
2001	Volkswagen, Daimler, Chevrolet	NO	NO

I used following query

SELECT 

GROUP_CONCAT (distinct table1.year, table1.Cars ASC Separator ',') as Car Model

FROM TABLE1

CodePudding user response：

Data

CREATE TABLE mytable(
   year INTEGER  NOT NULL 
  ,Cars VARCHAR(50) NOT NULL
);
INSERT INTO mytable
(year,Cars) VALUES 
(2000,'BMW'),
(2000,'Maruti Suzuki'),
(2000,'TOYOTA'),
(2001,'Volkswagen'),
(2001,'Daimler'),
(2001,'Chevrolet');

use IF and GROUP_CONCAT

select year,
GROUP_CONCAT(Cars) as Cars,
IF(GROUP_CONCAT(Cars) LIKE '%TOYOTA%','YES','NO')  TOYOTA,
IF(GROUP_CONCAT(Cars) LIKE '%FORD%','YES','NO')  FORD
  from mytable
group by year

dbfiddle

CodePudding user response：

This would be one way of doing it.

SELECT
    tmp.*,
    IF(
        LOCATE('TOYOTA',tmp.car_model,1),
        'YES',
        'NO'
    ) AS 'TOYOTA',
    IF(
        LOCATE('Ford',tmp.car_model,1),
        'YES',
        'NO'
    ) AS 'Ford'
FROM (
    SELECT
        year,
        GROUP_CONCAT(DISTINCT car ORDER BY car ASC) AS car_model
    FROM cars
    GROUP BY year
) tmp
ORDER BY year ASC

What you see here is a sub-query in action.

First, the part within FROM ( .... ) tmp creates a temporary table with two columns - year and car_model. The column car_model is a concatenated string with unique models.

Next, we query that temporary table, by using it as a source for our new query, which checks up whether TOYOTA and Ford are present in any of its rows.

However, if you need to check for every single car model, and having a separate IF column for each and every one of them, you would probably be better off dynamically creating your query with something like PHP, or any other server-side language which works with MySQL.