CODEWITHSUNDEEP
mysql
user2287892
user2287892

Reputation: 83

How can I group by different responsibles names for the same ID

Let me explain what I need: I have different name_user for a unique num_contract, however, it is showing one responsible by row.

SELECT a.num_contract, g.name_user
FROM `contract` AS a
INNER JOIN `resp_contract` g ON g.id_contract = a.id_contract
INNER JOIN `user` h ON h.id_user = g.id_user
ORDER BY `a`.`id_contract` DESC

OUTPUT

a.num_contract | g.name_user
0857             John
0857             Mark
0856             Ann
0855             Louis
0855             Peter
0855             Alisson

As you can see above, a.num_contract is unique and showing the name_user in each row.

What I expect:

a.num_contract | g.name_user
0857             John, Mark
0856             Ann
0855             Louis, Peter, Alisson

OR

a.num_contract | g.name_user | g.name_user | g.name_user
0857             John          Mark          NULL
0856             Ann           NULL          NULL  
0855             Louis         Peter         Alisson

I read something about pivot table, but I'm not sure how I can achieve it. Thank you,

Upvotes: 0

Views: 34

Answers (1)

Dark Knight
Dark Knight

Reputation: 6541

When wanting user list as a single column, you can use GROUP_BY with GROUP_CONCAT.

SELECT 
    a.num_contract AS Contract_No, 
    GROUP_CONCAT(g.name_user) AS Users
FROM `contract` AS a
INNER JOIN `resp_contract` g ON g.id_contract = a.id_contract
INNER JOIN `user` h ON h.id_user = g.id_user
GROUP BY a.num_contract
ORDER BY `a`.`id_contract` DESC;

Output:
Contract_No    | Users
--------------------------------------
0857           | John,Mark
0856           | Ann
0855           | Louis,Peter,Alisson

If you want to list the users as separate column then you can use rank variable or SUBSTRING_INDEX on above query to get the desired result. 

SELECT
    Contract_No,
    SUBSTRING_INDEX(Users,'$$',1) AS User_1,
    CASE WHEN Total_Users >=2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Users,'$$',2),' ',-1) END AS User_2,
    CASE WHEN Total_Users>=3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Users, '$$',3),' ',-1) END AS User_3
FROM (
    SELECT 
        a.num_contract AS Contract_No, 
        GROUP_CONCAT(g.name_user SEPARATOR '$$') AS Users,
        COUNT(0) AS Total_Users
    FROM `contract` AS a
    INNER JOIN `resp_contract` g ON g.id_contract = a.id_contract
    INNER JOIN `user` h ON h.id_user = g.id_user
    GROUP BY a.num_contract
    ORDER BY `a`.`id_contract` DESC
) AS t;

Output:
Contract_No    | User_1      | User_2     | User_3
-----------------------------------------------------
0857           | John        | Mark       |  
0856           | Ann         |            |
0855           | Louis       | Peter      |  Alisson

Upvotes: 1

Related Questions