SQL result in columns

Asked

Viewed 107 times

0

I have an appointment Oracle SQL that returns the records per row, and I wanted to turn that into column. I did a search and find that the Pivot does this, but I don’t know how to use it, someone can help me?

CURRENT RETURN:

SELECT
AFD.CODEMP AS CODEMP,
AFD.CODFUNC AS CODFUNC,
AFD.DTMOV,
AFD.HORA AS HORA,
OCO.DESCROCOR,
AFD.FECHADO
FROM
TFPAFDT AFD
LEFT JOIN TFPOCO OCO ON OCO.NUOCOR = AFD.NUOCOR 
WHERE
AFD.CODEMP = 1 
AND AFD.CODFUNC = 702 
AND (
    ( AFD.DTMOV BETWEEN TO_DATE ( '01/08/2017', 'DD-MM-YYYY' ) AND TO_DATE ( '30/08/2017', 'DD-MM-YYYY' ) ) 
    OR ( AFD.DTINIJORNADA BETWEEN TO_DATE ( '01/08/2017', 'DD-MM-YYYY' ) AND TO_DATE ( '30/08/2017', 'DD-MM-YYYY' ) ) 
)

RESULTADO DO RETORNO ATUAL

EXPECTED RETURN:

I would like the return lines to come the date of the movement and the marking times as column, as example below.

Dt. Mov.   | Dt1 | Dt 2 | Dt 3 | Dt 4<br>
23/08/2017 | 800 | 1206 | 1311 | 1802
sql oracle
  • 1

    What’s the need for that? If this is just one way to view the returns from a look here https://answall.com/questions/225916/return

  • @R.Santos I need each record of 'Time' in a column, in the form of string_agg (oracle), it brings all the records and one column only. Thank you.

  • Does it have any field indicating that the type of input/output , or is just the position , the position being an artificial sequence could be created by "Analytic functions" .

  • @Motta has yes! There is a column with the information 'E (input)' and’S (output)'.

  • Still complicates because they are two tags for fourth markings , apart from the markings of the staff who turns the night , vigias , callcenters etc, already used Analytic functions ?

  • @Motta In addition to the 'E' and’S' column I have another column with the information '1' and '2' to indicate the first and second rounds. As for the night-time appointments and so on, it doesn’t exist on my set.

  • Pivot sql seems to be simple so , tomorrow , if you have time , I put something.

  • @Motta ok, I stand by. I thank you in advance.

  • Let’s go continue this discussion in chat.

Show 4 more comments

2 answers

0

--a solution with CASE and MAX --the case makes pivot and max to secure in just one line --assuming the names of the columns

SELECT
AFD.CODEMP AS CODEMP,
AFD.CODFUNC AS CODFUNC,
AFD.DTMOV,
max(case when turno = 1 and tipo = 'E' then AFD.HORA else null end) hr1,
max(case when turno = 1 and tipo = 'S' then AFD.HORA else null end) hr2,
max(case when turno = 2 and tipo = 'E' then AFD.HORA else null end) hr3,
max(case when turno = 2 and tipo = 'S' then AFD.HORA else null end) hr4
FROM
TFPAFDT AFD
LEFT JOIN TFPOCO OCO ON OCO.NUOCOR = AFD.NUOCOR 
WHERE
AFD.CODEMP = 1 
AND AFD.CODFUNC = 702 
AND (
    ( AFD.DTMOV BETWEEN TO_DATE ( '01/08/2017', 'DD-MM-YYYY' ) AND TO_DATE ( '30/08/2017', 'DD-MM-YYYY' ) ) 
    OR ( AFD.DTINIJORNADA BETWEEN TO_DATE ( '01/08/2017', 'DD-MM-YYYY' ) AND TO_DATE ( '30/08/2017', 'DD-MM-YYYY' ) ) 
)
group by AFD.CODEMP,
         AFD.CODFUNC,
         AFD.DTMOV

  • worked, but in cases that have record of occurrence it duplicates the data the record (the date). The right one would be for each period, a column that informs the occurrence, if it exists, or leave blank, if null.

  • What would be "occurrence record" ?

  • occurrence (justification) if the collaborator does not record any point.

  • will be null , this "occurrence" is another table

0

Here I have found several examples of what you are looking to do. I think the simplest is using the LISTAGG function, but groups all the information in one column. Check the link if this is not what you want.

Code:

SELECT AFD.DTMOV,
LISTAGG (AFD.HORA, ',') 
WITHIN GROUP 
(ORDER BY AFD.HORA) Horas
FROM TFPAFDT AFD
GROUP BY AFD.DTMOV

  • It is also a solution but not "column"

Browser other questions tagged sql oracle

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