Vallavan J Vallavan J - 5 months ago 7
PHP Question

How to separate the row value one by one with count function option in mysql

Mysql Table:
In My facility table is this

facility_name mbid date
yoga,aerobics,table tennis,tai chi, OM1111 2016-06-12
aerobics,tai chi, OM1111 2016-06-12


How to split row value one by one with mbid in mysql:

Facility_name mbid Number of count
yoga OM1111 1
aerobics OM1111 2
table tennis OM1111 1
tai chi OM1111 2

Answer
CREATE TABLE facility 
    (facility_name varchar(35), mbid varchar(6), date varchar(10))
;

INSERT INTO facility 
    (facility_name, mbid, date)
VALUES
    ('yoga,aerobics,table tennis,tai chi,', 'OM1111', '2016-06-12'),
    ('aerobics,tai chi,', 'OM1111', '2016-06-12')
;

Script :

Select T.VALUE,T.mbid,COUNT(T.VALUE)Cnt FROM (
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.facility_name, ',', n.n), ',', -1) value,mbid
  FROM facility  t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.facility_name) - LENGTH(REPLACE(t.facility_name, ',', ''))))T
 WHERE T.VALUE <> ''
 GROUP BY T.VALUE,T.mbid
 ORDER BY T.value
Comments