Question 21602 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get the particular value from the GROUP_CONTACT column – Mysql

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
Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    Simply combine a group_concat aggregate with standard conditional aggregation:

    select Year, GROUP_CONCAT(distinct cars order by cars asc) CarModel,
    max(case when cars = 'TOYOTA' then 'Yes' else 'No' end) TOYOTA,
    max(case when cars = 'FORD' then 'Yes' else 'No' end) FORD
from t
group by year;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search