Consolidate all columns into one MYSQL row

Asked

Viewed 330 times

0

I’m trying to make a query that groups all the results in one line, that is, that does not bring duplicated results. I’ve used the distinct and the group bybut one of the two has returned what I need. My consultation is as follows::

SELECT TICKET, DATAHORA, TIPO, ESTADO, CRITICIDADE, LOGIN, PARECER, INFOPEND, PROP, DATAPROP, PREV, SRD, PROT, REAB, PRIENC
FROM (
    SELECT DISTINCT TC.TN TICKET, TC.CREATE_TIME DATAHORA,  TT.NAME TIPO, TS.NAME ESTADO, TP.NAME CRITICIDADE, US.LOGIN LOGIN,
    CASE  
        WHEN TH.NAME LIKE  '%PARECER%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PARECER,
    CASE  
        WHEN TH.NAME LIKE  '%INFORMACOESPENDENTES%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END INFOPEND,
    CASE  
        WHEN AR.A_SUBJECT LIKE  '%ATUALIZAÇÃO PROPRIETÁRIO%' THEN LEFT(AR.A_FROM, POSITION('<' IN (AR.A_FROM))-1)
        ELSE ''
        END PROP,
    CASE  
        WHEN AR.A_SUBJECT LIKE  '%ATUALIZAÇÃO PROPRIETÁRIO%' THEN (AR.CREATE_TIME)
        ELSE ''
        END DATAPROP,
    CASE  
        WHEN TH.NAME LIKE  '%PREVISAOATENDIMENTO%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PREV,
    CASE  
        WHEN TH.NAME LIKE  '%SRD%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END SRD,
    CASE  
        WHEN TH.NAME LIKE  '%PROTOCOLO%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PROT,
   CASE  
        WHEN TS.NAME LIKE  '%REABERTO' THEN (TS.NAME)
        ELSE ''
        END REAB,
   CASE 
        WHEN AR.A_SUBJECT LIKE 'FECHAR' THEN AR.CREATE_TIME
        ELSE ''
        END PRIENC

 FROM OTRS2.TICKET TC
 INNER JOIN OTRS2.TICKET_TYPE TT ON TC.TYPE_ID = TT.ID
 INNER JOIN OTRS2.TICKET_STATE TS ON TC.TICKET_STATE_ID = TS.ID
 INNER JOIN OTRS2.TICKET_PRIORITY TP ON TC.TICKET_PRIORITY_ID=TP.ID
 INNER JOIN OTRS2.USERS US ON TC.CREATE_BY=US.ID
 INNER JOIN OTRS2.TICKET_HISTORY TH ON TC.ID=TH.TICKET_ID 
 INNER JOIN OTRS2.ARTICLE AR ON TC.ID=AR.TICKET_ID
)A

This query is generating the following result: Resultado da query gerada

However, I need the ticket number not to be repeated, that is, in a row it aggregates all the columns. If I use the group by ticket happens the following: Resultado do group by

With, the group by ticket, it returns only one row, but the column information (like for example the column seems) goes away! How can I put all information on one line? Thank you

mysql sql group-by distinct

  • If it has no aggregation function, I don’t see why to use group by, I think it’s better to be distinct. after that, you would have to see this lot of case, because it seems to me unnecessary... for a better analysis, you have to provide the structure of the tables and some sample data. It would be very useful to put in Sqlfiddle

  • If you want to group as it is in the second figure, you need to include all columns to group: group by ticket, datahora, tipo, estado, criticidade, login,.... If any of them make a difference, it’ll bring more than one line. See that "look" has the empty value and "kkkkkk", it will generate two rows, and so on with the other columns

1 answer

0

I’ve seen you want something like this: where I wear one alias of the bank as d, tested in mysql

`select d.TICKET AS TICKET,group_concat(d.PARACER separator ', ') AS PARECER from banco d group by d.PARECER`

I’ll have a way out like this:

+---+-----------------------------+
| TICKET |-OPINION-----+
+---+-----------------------------+
| 201822 | kkkkkkkk,kkkkkkkk |
+----+----------------------------+

add the other desired columns in the same way

Browser other questions tagged mysql sql group-by distinct

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