Concatenate Lines with the same User

Asked

Viewed 92 times

0

I have the following consultation:

SELECT e.id_taxe, u.nm_user, dt_taxe, SUM(e.vl_taxe) as vl_taxe
FROM taxe as e 
INNER JOIN user as u ON u.id_user = e.id_user 
WHERE id_enterprise = 86 AND (dt_taxe BETWEEN '2017-01-01' AND '2017-03-31') 
AND lg_cancel = 0 GROUP BY e.dt_taxe, e.id_user ORDER BY e.id_user,e.dt_taxe

Who returns to me :

id_taxe     nm_user   dt_taxe      vl_taxe
728         Maria     2017-01-01   17091.07048034668
727         Maria     2017-02-01   14091.07048034668
721         Maria     2017-03-01   1021.07048034668
731         Pedro     2017-01-01   16353.569854736328
732         Pedro     2017-02-01   6353.56231239

How can I concatenate the fields of the same user on the same line, to have the following result:

id_taxe     nm_user   dt_taxe      vl_taxe
728         Maria     2017-01-01   17091.07048034668 , 
                      2017-02-01   14091.07048034668,
                      2017-03-01   1021.07048034668
731         Pedro     2017-01-01   16353.569854736328,
                      2017-02-01   6353.56231239
mysql sql select group-concat

  • Bia, conceptually you have 3 records with distinct id_taxe, Note that your id_taxe in the example was fixed at 728, and Maria has others (727 e 721)

  • @Najibelalam actually id does not matter to me, only date and value

1 answer

0

good, considering the following structure 

CREATE TABLE user
    (`id_user` int, `nome` varchar(5))
;

INSERT INTO user
    (`id_user`, `nome`)
VALUES
    (1, 'maria')
;

INSERT INTO user
    (`id_user`, `nome`)
VALUES
    (2, 'pedro')
;


**tabela de taxas** 
CREATE TABLE taxe
    (`id_user` int, `id_taxe` int, `data` varchar(11), `taxe` varchar(9))
;

INSERT INTO taxe
   (`id_user`, `id_taxe`, `data`, `taxe`)
VALUES
    (1, 1, '2017-01-01', '17091.070')
;

INSERT INTO taxe
   (`id_user`, `id_taxe`, `data`,`taxe`)
VALUES
    (1, 2, '2017-02-01', '121222.70')
;

INSERT INTO taxe
   (`id_user`, `id_taxe`, `data`,`taxe`)
VALUES
    (1, 3, ' 2017-03-01', '999922.70')
;


INSERT INTO taxe
   (`id_user`, `id_taxe`, `data`, `taxe`)
VALUES
    (2, 4, '2017-01-01', '16353.564')
;

INSERT INTO taxe
   (`id_user`, `id_taxe`, `data`,`taxe`)
VALUES
    (2, 5, '2017-02-01', '6353.562')
;

**would be more or less like your query, using group_concat **

select u.id_user, group_concat(
DISTINCT u.nome
    ORDER BY u.nome DESC SEPARATOR ' '
) as nome, 

group_concat(
  DISTINCT t.data
ORDER BY t.data DESC SEPARATOR ',') as data, 

group_concat(
  DISTINCT t.taxe
ORDER BY t.taxe DESC SEPARATOR ',') as taxes 

from user u
inner join taxe t
on t.id_user = u.id_user
group by u.id_user;

with the result 

id_user nome    data    taxes
1   maria   2017-02-01,2017-01-01, 2017-03-01   999922.70,17091.070,121222.70
2   pedro   2017-02-01,2017-01-01   6353.562,16353.564

I hope I helped. Hug :=)

follows link of execution on Fiddle

Browser other questions tagged mysql sql select group-concat

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