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.
Advertisement
Answer
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