SQL result in columns

Question

SQL result in columns

Asked 7 years, 2 months ago

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

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

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

2017/09/19 at 20:41
@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.

– Tiago Martins

2017/09/19 at 20:49
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

2017/09/19 at 21:44
@Motta has yes! There is a column with the information 'E (input)' and’S (output)'.

– Tiago Martins

2017/09/19 at 23:16
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

2017/09/19 at 23:22
@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.

– Tiago Martins

2017/09/19 at 23:24
Pivot sql seems to be simple so , tomorrow , if you have time , I put something.

– Motta

2017/09/20 at 00:42
@Motta ok, I stand by. I thank you in advance.

– Tiago Martins

2017/09/20 at 11:28
Let’s go continue this discussion in chat.

– Tiago Martins

2017/09/20 at 15:16

Show 4 more comments

2 answers

Browser other questions tagged sql oracle

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

by Motta • **3,568** points · Answer 1 · 2017-09-20T13:19:32+00:00

--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

by Claudio Lopes • **380** points · Answer 2 · 2017-09-20T14:31:08+00:00

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