Concatenate Lines with the same User

Question

Concatenate Lines with the same User

Navigation

#1 by (0 votes)

0

I have the following query:

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

That returns 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 same user's fields 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

asked by anonymous 13.03.2017 / 19:11

1 answer

DropdownList on the Edit screen HTML is not calling function - JS

score 0 · Answer 1

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')
;

** It would be more or less 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 have helped. Thanks!

follows the execution link in Fiddle