SQL result in columns

Question

SQL result in columns

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have an Oracle SQL query that returns records per line, and I wanted to turn this into column. I've researched and found that Pivot does this, but I'm not sure how to use it, can anyone 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 from the date of the move and the marking hours as a column, as shown below.

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

sql oracle

asked by anonymous 19.09.2017 / 22:35

2 answers

Avoid getting Favicon in Flask Error in MongoDB / Mongoose?

score 0 · Answer 1

- a solution with CASE and MAX --the case makes the pivot and the max to guarantee in only one line --using the column names

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

score 0 · Answer 2

Here I found several examples of what you are looking for. I think the simplest one is using the LISTAGG function, but it 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