Please anyone help about my case in mysql query

Question

I have this data..

+------+--------------+------------+
+  id  +  position_id + name       +
+------+--------------+------------+
+  1   +  1           + name_1     +
+  2   +  5           + name_2     +
+  3   +  2           + name_3     +
+  4   +  2           + name_4     +
+  5   +  2           + name_5     +
+  6   +  3           + name_6     +
+  7   +  4           + name_7     +
+  8   +  3           + name_8     +
+  9   +  2           + name_9     +
+------+--------------+------------+

..then I want the the results is like

+--------------+-----------+----------+----------+-----------+
+  position_id +  result1  + result2  +  result3 +  result4  +
+--------------+-----------+----------+----------+-----------+
+  1           +  name_1   +  @       +  @       +  @        +
+  2           +  name_3   +  name_4  +  name_5  +  name_9   +
+  3           +  name_6   +  name_8  +  @       +  @        +
+  4           +  name_7   +  @       +  @       +  @        +
+  5           +  name_2   +  @       +  @       +  @        +
+--------------+-----------+----------+----------+-----------+

I have some case for resulting data, this data is for my school reports. The data results must be dynamic following primary of position and if the result is empty will sowing @.

For more data or information you can ask with the following command

number of column is also dynamic? Means do you have result5 for some cases? — Charvee Shah, Dec 19 '14 at 09:39
Not the same, but how about: `select position_id, group_concat(name) as names from your_table group by position_id`? — juergen d, Dec 19 '14 at 09:40
@CharveeShah yes, for more results will showing result5. please solved this case ?!! — Arr, Dec 19 '14 at 09:42
In that case you can't do this with mysql query only.You need to do some coding too. First you have to take maximum count of position and then u can write dynamic query for this. — Charvee Shah, Dec 19 '14 at 09:44
possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Filburt, Dec 19 '14 at 09:45
@juergend thank you, I have to try your query and the results is true for this case. But I want the results is like the sample above, if new result will automatically create new column. — Arr, Dec 19 '14 at 09:47
With GROUP_CONCAT you would just explode out the resulting column into multiple columns in your calling script. — Kickstart, Dec 19 '14 at 09:49
@Kickstart thank you, yes, I think you are true. But this query is for jasper report, so I can't include programming language or even include explode syntax. But thank you for you suggestion :) — Arr, Dec 19 '14 at 10:00
I have no knowledge of Jasper at all. However conventional SQL will return a fixed number of columns. I think you would need to use a MySQL procedure to generate a result set with a variable number of columns. — Kickstart, Dec 19 '14 at 10:06

Kickstart · Accepted Answer · 2014-12-19T11:45:21.763

If a fixed number of columns then you could do something like this:-

SELECT a.position_id, 
        COALESCE(MIN(a.name), '@') AS result1,
        COALESCE(MIN(b.name), '@') AS result2,
        COALESCE(MIN(c.name), '@') AS result3,
        COALESCE(MIN(d.name), '@') AS result4
FROM some_table a
LEFT OUTER JOIN some_table b ON a.position_id = b.position_id AND a.id < b.id
LEFT OUTER JOIN some_table c ON a.position_id = c.position_id AND b.id < c.id
LEFT OUTER JOIN some_table d ON a.position_id = d.position_id AND c.id < d.id
GROUP BY a.position_id

With a variable number of columns it isn't going to be possible really without dynamically creating the SQL based on the number of columns, or doing something nasty with GROUP_CONCAT.

But this isn't likely to be efficient.

It would probably be better to do a query to get the first results and then sort out the formatting in the calling script.

EDIT

Time for some nasty code, and i still needs polishing!

First bit is a stored procedure. This gets the max number of columns (gets it slightly wrong, but should be easy to fix with a bit of effort, and works for now) and dynamically builds up the SQL to create a temp table with this number of columns, and then populates it.

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN

    DECLARE sql1 TEXT;
    DECLARE sql2 TEXT;
    DECLARE sql3 TEXT;
    SET @@group_concat_max_len = 32000;

    SELECT 
            GROUP_CONCAT(CONCAT('MIN(a', (1 + units.iCnt + 10 * tens.iCnt), '.name) AS result', (1 + units.iCnt + 10 * tens.iCnt)) ORDER BY (1 + units.iCnt + 10 * tens.iCnt)),
            GROUP_CONCAT(CONCAT('LEFT OUTER JOIN some_table a', (1 + units.iCnt + 10 * tens.iCnt), ' ON  a', (units.iCnt + 10 * tens.iCnt), '.position_id =  a', (1 + units.iCnt + 10 * tens.iCnt), '.position_id AND a', (units.iCnt + 10 * tens.iCnt), '.id <  a', (1 + units.iCnt + 10 * tens.iCnt), '.id') ORDER BY (1 + units.iCnt + 10 * tens.iCnt) SEPARATOR ' '),
            GROUP_CONCAT(CONCAT('result',(1 + units.iCnt + 10 * tens.iCnt), ' VARCHAR(255)') ORDER BY (1 + units.iCnt + 10 * tens.iCnt))
    INTO sql1, sql2, sql3
    FROM
        (
        SELECT MAX(count_name) as max_count_name
        FROM
        (
            SELECT COUNT(name) as count_name
            FROM some_table
            GROUP BY position_id
        ) sub0
    ) sub1,
    (SELECT 1 iCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units,
    (SELECT 1 iCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
    WHERE max_count_name >= (units.iCnt + 10 * tens.iCnt);

    DROP TEMPORARY TABLE IF EXISTS temp1;

    SET @sqlmain1 = CONCAT('CREATE TEMPORARY TABLE temp1(position_id INT, result0 VARCHAR(255), ', sql3, ')');
    PREPARE stmt FROM @sqlmain1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;    

    SET @sqlmain2 = CONCAT('INSERT INTO temp1 SELECT a0.position_id, MIN(a0.name) AS result0,', sql1, ' FROM some_table a0 ', sql2, ' GROUP BY a0.position_id ');

    PREPARE stmt FROM @sqlmain2;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;    
END;;
DELIMITER ;

You can then execute this and then select from the resulting temp table. Note that both statements must be done in the same SQL session, otherwise the temp table will have disappeared by the time you do the select:-

CALL stored_procedure_name();
SELECT * FROM temp1

Hopefully you can pass these both to Jasper.

thank you, it's really help :). but this case must be dynamic, so can you suggest about the true table field ? — Arr, Dec 19 '14 at 09:55
@Aik - I have edited the answer with a possible solution using a MySQL procedure. — Kickstart, Dec 19 '14 at 11:46

Please anyone help about my case in mysql query

1 Answers1

Linked