MySQL GROUP_CONCAT with COLUMN SPLIT

Question

I am working with a TABLE, need logical help.

Check the below URL for the table structure and sample data.

http://sqlfiddle.com/#!2/ece06/2

Table Schema:

CREATE TABLE test (
  ID INTEGER,
  NAME VARCHAR (50),
  VALUE INTEGER
);

Inserted Data:

INSERT INTO test VALUES (1, 'A', 4);
INSERT INTO test VALUES (1, 'B', 5);
INSERT INTO test VALUES (1, 'C', 8);
INSERT INTO test VALUES (2, 'D', 9);
INSERT INTO test VALUES (2, 'E', 9);
INSERT INTO test VALUES (3, 'F', 9);
INSERT INTO test VALUES (3, 'G', 9);
INSERT INTO test VALUES (3, 'H', 9);
INSERT INTO test VALUES (3, 'I', 9);

Query:

SELECT ID, GROUP_CONCAT(NAME) AS CODE
FROM test
GROUP BY ID;

OutPUT:

ID  CODE
1   A,B,C
2   D,E
3   F,G,H,I

Expected OUTPUT:

ID  CODE   CODE   CODE  CODE
1    A      B      C    NULL
2    D      E     NULL  NULL
3    F      G      H     I

As you can see the output of the query has concat with comma. And currently we are doing string concat using PHP which splits while displaying!!

Is there any other way to split the RESULT and show each value in column and same ROW? In same result?

Note: The CODE might vary for each ROW.

Solution 1

With Help from my colleague We arrived at a point to solve this senario. Hope some one might needed it.. It is welcome if some one make it much simpler.

BEGIN
SET @v=0;


SET @v1=0;


SELECT tmp.cnt INTO @v
FROM
  (SELECT Id,
          count(ID) AS cnt,
          GROUP_CONCAT(name)
   FROM test
   GROUP BY id) tmp
ORDER BY tmp.cnt DESC LIMIT 1;


SET @str=' ';

 WHILE(@v>@v1) DO
SET @v1=@v1+1;

 IF(@str='') THEN
SET @str=CONCAT(@str,'ID, REPLACE(SUBSTRING(SUBSTRING_INDEX(GROUP_CONCAT(NAME), '','',', @v1,'),LENGTH(SUBSTRING_INDEX(GROUP_CONCAT(NAME),'','',', @v1,'-1)) + 1),'','','''') AS Code' ,@v1);

 ELSE
SET @str= CONCAT(@str,',REPLACE(SUBSTRING(SUBSTRING_INDEX(GROUP_CONCAT(NAME),'','',', @v1,'),LENGTH(SUBSTRING_INDEX(GROUP_CONCAT(NAME),'','',' , @v1,' -1)) + 1),'','','''') AS Code',@v1);

 END IF;

 END WHILE;

SET @str=CONCAT('SELECT ' , @str, ' FROM test GROUP BY ID');

 PREPARE MYSQLQUERY
FROM @str;

 EXECUTE MYSQLQUERY;

 DEALLOCATE PREPARE MYSQLQUERY;

 END

Solution 2

If you know the number of GROUP_CONCAT entries(I mean 3 fields are combined in the case of ID =1 and 2 fields are combined in the case of 2 etc), then there is a dirty way.

SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 1), ',', -1) AS CODE1,
If(  length(GROUP_CONCAT(NAME)) - length(replace(GROUP_CONCAT(NAME), ',', ''))>1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 2), ',', -1) ,NULL) 
           as CODE2,
   SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 3), ',', -1) AS CODE3
FROM test
GROUP BY ID;

Output:

ID  CODE1   CODE2   CODE3
1   A          B    C
2   D       (null)  E
3   F          G    H

The above query assume that you are GROUP_CONCAT-ing 3 fields. If you are dynamically generating query you can give a try. SQLFIDDLE

EDIT: Note: The CODE might vary for each ROW.( Ignoring this)

Comments

[Mosty Mostacho]

Based on your expected output I would go for PHP. Just iterate over the results and create the extra "columns" dynamically in your resultset.

[pbarney]

This is very good. It would be nice if we could JOIN with the resulting table, but I think that's beyond MySQL's capabilities.