In mysql it is possible to create an extra line if 2 or more lines are equal

Asked

Viewed 33 times

0

Hello I wonder if it is possible to create a fourth line for mysql based on the Titles that repeat as in the first table where the game The Witcher\1743: Wild Hunt repeats 3 times and is already generated to 4 line based on the second table ?

<table>
    <thead>
        <tr>
            <th>Título</th>
            <th>Plataforma </th>
            <th>Preço</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>The Witcher 3: Wild Hunt</td>
            <td>Pc</td>
            <td>R$ 50</td>          
        </tr>
        <tr>
            <td>The Witcher 3: Wild Hunt</td>
            <td>XOne</td>
            <td>R$ 67</td>          
        </tr>
        <tr>
            <td>The Witcher 3: Wild Hunt</td>
            <td>Ps4</td>
            <td>R$  80</td>     
        </tr>
    </tbody>    
</table>
<h1>Tabela de Exemplo abaixo</h1>
<table>
<thead>
    <tr>
        <th>Título</th>
        <th>Plataforma </th>
        <th>Preço</th>
    </tr>
    <tbody>
        <tr>
            <td>The Witcher 3: Wild Hunt</td>
            <td>Pc/XOne/Ps4</td>
            <td colspan="3" >R$ 50(PC) R$ 67(XOne) R$ 80(Ps4)</td>      
        </tr>
</thead>
</table>
mysql

1 answer

2


Hello,

You need to group the columns by the game title, in this case just use the GROUP BY together with the GROUP_CONCAT.

Ex:

SELECT
    titulo,
    GROUP_CONCAT(plataforma SEPARATOR ', ') AS plataforma,
    GROUP_CONCAT(preco SEPARATOR ', ') AS preco
FROM
    jogos
GROUP BY
    titulo;

If you want to bring the separate records and at the end, in the same query, bring a grouped row, you can make the two querys and join by UNION.

Ex:

SELECT
    titulo,
    plataforma,
    preco
FROM
    jogos
UNION ALL
SELECT
    titulo,
    GROUP_CONCAT(plataforma SEPARATOR ', ') AS plataforma,
    GROUP_CONCAT(preco SEPARATOR ', ') AS preco
FROM
    jogos
GROUP BY
    titulo;

Here’s a fiddle with these examples in action: https://www.db-fiddle.com/f/d9Nndvt5Eix1RNW7RMJo7C/0

Read more about: GROUP BY GROUP_CONCAT UNION

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.