MySQL - multi-row by 1 id & converting multi-column data into a comma delimited row

Question

I have a database table:
requesting persons ID column that separates each request (reqid)
category ID column that separates into different categories each item that's in the request (catid)
an item ID column that separates each item type within the category (itemid)
an item name column that lists the name of the item (item)
an item descriptor column that gives a description of the item (desc)
and amount column that gives the total cost of each item in that category (amount)

so my table looks like this:

|---------|-------|----------|--------|-------|------------|
|  reqid  | catid | itemid   | item   | desc  | amount     | 
|---------|-------|----------|--------|-------|------------|
|  1      | 3     | 16       | food   | food  | 200        | 
|---------|-------|----------|--------|-------|------------|
|  1      | 3     | 17       | water  | wtr   | 50         | 
|---------|-------|----------|--------|-------|------------|
|  1      | 3     | 18       | film   | film  | 20         | 
|---------|-------|----------|--------|-------|------------|
|  1      | 5     | 30       | room   | room  | 500        | 
|---------|-------|----------|--------|-------|------------|
|  1      | 5     | 31       | chair  | chair | 150        | 
|---------|-------|----------|--------|-------|------------|
|  2      | 3     | 16       | food   | food  | 200        | 
|---------|-------|----------|--------|-------|------------|
|  2      | 3     | 17       | water  | wtr   | 50         | 
|---------|-------|----------|--------|-------|------------|
|  3      | 3     | 18       | film   | film  | 20         | 
|---------|-------|----------|--------|-------|------------|
|  3      | 5     | 30       | room   | room  | 500        | 
|---------|-------|----------|--------|-------|------------|
|  3      | 5     | 31       | chair  | chair | 150        | 
|---------|-------|----------|--------|-------|------------|

And i want my query result to look like

|--------|------------------|----------------------|--------------------|--------------------|
| reqid  | catid3itemid     | catid3item           | catid3desc         | catid3amount       | 
|--------|------------------|----------------------|--------------------|--------------------|
|  1     |16, 17, 18        | food, water, film    | food, wtr, film    | 200, 50, 20        | 
|--------|------------------|----------------------|--------------------|--------------------|
|  2     |16, 17            | food, water          |food, wtr           | 200, 50,           | 
|--------|------------------|----------------------|--------------------|--------------------|
|  3     |18                | film                 | film               | 20                 | 
|--------|------------------|----------------------|--------------------|--------------------|

continued:

|------------------|----------------------|--------------------|--------------------|
| catid4itemid     | catid4item           | catid4desc         | catid4amount       | 
|------------------|----------------------|--------------------|--------------------|
|                  |                      |                    |                    | 
|------------------|----------------------|--------------------|--------------------|
|                  |                      |                    |                    | 
|------------------|----------------------|--------------------|--------------------|
|                  |                      |                    |                    | 
|------------------|----------------------|--------------------|--------------------|

continued final:

|------------------|----------------------|--------------------|--------------------|
| catid5itemid     | catid5item           | catid5desc         | catid5amount       | 
|------------------|----------------------|--------------------|--------------------|
|30,31             |room, chair           |room, chair         | 500, 150           | 
|------------------|----------------------|--------------------|--------------------|
|                  |                      |                    |                    | 
|------------------|----------------------|--------------------|--------------------|
|30,31             |room, chair           |room, chair         | 500, 150           | 
|------------------|----------------------|--------------------|--------------------|

I've been seeing posts about using GROUP_CONCAT or CROSS APPLY or SWITCH STATEMENT

For instance GROUP_CONCAT:

select *, GROUP_CONCAT(`table`.`categoryid` ORDER BY `table`.`categoryid` ASC SEPARATOR ', ') AS `categoryid`
from `table` 
GROUP BY `table`.`requestid`

but this lumps everything into a column not separated by the category id , but all the category ids together in one column.

Any suggestions or help would be much appreciated.

You need to pivot the table to get each category into its own set of columns. — Barmar, Mar 06 '19 at 20:51
See https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Barmar, Mar 06 '19 at 20:55
Sorry, I am very new to coding and so I've been trying to search on forums for an answer but nothing really fits so please have patience with my lack of knowledge. Is there a way to use IF statements in a query in regards to IF categoryid = 1 THEN group_concat everything that has categoryid =1 within the same requestid be comma separated for columns item, descriptor and amount? — d suh, Mar 06 '19 at 23:07
Yes, you can use an if within group_concat but, if it is to display in an application, I suggest it be done in application code, as @Strawberry suggested. Anyway I will post a more complete answer on how to get with sql. — dh117, Mar 07 '19 at 00:27

score 0 · Answer 1 · answered Mar 07 '19 at 00:32

thank you all for the help! i figured it out without using pivot tables

select table.requestid AS request_id, group_concat((case when (table.categoryid = 2) then table.item end) separator ',') AS item2 ,group_concat((case when (table.categoryid = 2) then table.descriptor end) separator ',') AS descriptor2, group_concat((case when (table.categoryid = 2) then table.amount end) separator ',') AS amount2, group_concat((case when (table.categoryid = 3) then table.item end) separator ',') AS item3, group_concat((case when (table.categoryid = 3) then table.descriptor end) separator ',') AS descriptor3, group_concat((case when (table.categoryid = 3) then table.amount end) separator ',') AS amount3 group_concat((case when (table.categoryid = 4) then table.item end) separator ',') AS item4, group_concat((case when (table.categoryid = 4) then table.descriptor end) separator ',') AS descriptor4, group_concat((case when (table.categoryid = 4) then table.amount end) separator ',') AS amount4 group_concat((case when (table.categoryid = 5) then table.item end) separator ',') AS item5, group_concat((case when (table.categoryid = 5) then table.descriptor end) separator ',') AS descriptor5, group_concat((case when (table.categoryid = 5) then table.amount end) separator ',') AS amount5

from table

where ((table.categoryid = 2) or (table.categoryid = 3) or (table.categoryid = 4) or (table.categoryid = 5))

group by table.requestid

score 0 · Answer 2 · answered Mar 07 '19 at 00:42

If the categories were fixed (3, 4 and 5) you could use this query:

select 
reqid,
group_concat(distinct if(catid = 3, itemid, null)) as catid3itemid, 
group_concat(distinct if(catid = 3, item, null)) as catid3item, 
group_concat(distinct if(catid = 3, `desc`, null)) as catid3desc, 
group_concat(distinct if(catid = 3, amount, null)) as catid3amount , 
group_concat(distinct if(catid = 4, itemid, null)) as catid4itemid, 
group_concat(distinct if(catid = 4, item, null)) as catid4item, 
group_concat(distinct if(catid = 4, `desc`, null)) as catid4desc, 
group_concat(distinct if(catid = 4, amount, null)) as catid4amount , 
group_concat(distinct if(catid = 5, itemid, null)) as catid5itemid, 
group_concat(distinct if(catid = 5, item, null)) as catid5item, 
group_concat(distinct if(catid = 5, `desc`, null)) as catid5desc, 
group_concat(distinct if(catid = 5, amount, null)) as catid5amount 
from tab 
group by reqid;

If it has to be dynamic, a procedure can be used. Below I have used a repetition structure to generate a query that returns the desired structure for each catid (minimum to maximum) and executes it with prepared statment:

-- only to avoid problem with only_full_group_by
set global sql_mode = "";

create table tab (reqid int, catid int, itemid int, item varchar(10), `desc` varchar(20), amount int);
insert into tab values
(1, 3, 16, 'food', 'food', 200),
(1, 3, 17, 'water', 'wtr', 50),
(1, 3, 18, 'film', 'film', 20),
(1, 5, 30, 'room', 'room', 500),
(1, 5, 31, 'chair', 'chair', 150),
(2, 3, 16, 'food', 'food', 200),
(2, 3, 17, 'water', 'wtr', 50),
(3, 3, 18, 'film', 'film', 20),
(3, 5, 30, 'room', 'room', 500),
(3, 5, 31, 'chair', 'chair', 150);

delimiter $$
CREATE PROCEDURE result()
BEGIN

    DECLARE i INT DEFAULT (select min(catid) from tab);
    DECLARE iEnd INT DEFAULT (select max(catid) from tab);

    SET @sQuery = 'select reqid';

    WHILE i <= iEnd DO

        set @sQuery = CONCAT(@sQuery, 
            ', group_concat(distinct if(catid = ',i,', itemid, null)) as catid',i,'itemid,
            group_concat(distinct if(catid = ',i,', item, null)) as catid',i,'item, 
            group_concat(distinct if(catid = ',i,', `desc`, null)) as catid',i,'desc, 
            group_concat(distinct if(catid = ',i,', amount, null)) as catid',i,'amount'
        );

        SET i = i + 1;
    END WHILE; 

    SET @sQuery = CONCAT(@sQuery, ' from tab group by reqid');

    PREPARE stmt FROM @sQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$


call result();

Note: Because you did not give too many details and as has already been said in the comments, it might be better to perform the display logic in the application.

MySQL - multi-row by 1 id & converting multi-column data into a comma delimited row

2 Answers2