mysql - GROUP_CONCAT rows into a string

Question

I have a following result set:

req_id |  p_id  | ai_result  |  hash | sku
1      |  4     | Match      |  XAN  | HOW
1      |  4     | Match      |  HXN  | HOW
1      |  4     | Non Match  |  123  | HOW

I need to have the following output

sku  | matched  |  non_matched
HOW  | XAN, HXN |  123

Here's as far as I could get:

SELECT sku, GROUP_CONCAT(hash) AS hash
FROM `sku_match` 
GROUP BY req_id, p_id

How can I distinguish rows based on ai_result column and put them separately. Something like GROUP_CONCAT(hash) AS matched, GROUP_CONCAT(hash) AS non_matched?

you can try subquery

M.Hemant
– M.Hemant

2019-06-19 05:32:41 +00:00
Commented Jun 19, 2019 at 5:32 — M.Hemant
– M.Hemant, Commented Jun 19, 2019 at 5:32

Tim Biegeleisen · Accepted Answer · 2019-06-19 05:32:58Z

5

Try using conditional aggregation:

SELECT
    sku,
    GROUP_CONCAT(CASE WHEN ai_result = 'Match' THEN hash END) AS matched,
    GROUP_CONCAT(CASE WHEN ai_result = 'Non Match' THEN hash END) AS non_matched
FROM sku_match
GROUP BY
    sku;

Demo

answered Jun 19, 2019 at 5:32

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql - GROUP_CONCAT rows into a string

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related