Accumulate multiple rows in 1

Navigation
0

How can I generate something like an array from a select? , we are not allowed to create procedures or functions in the database.

I expose the problem. 

$Query = "select to_char(ld.diario_id) diario_id,COLABORADOR_ID from XXQP.XXQP_DIARIO_LN_TBL ld inner join XXQP.XXQP_DIARIO_HD_TBL d on ld.diario_id=d.diario_id
WHERE 1 = 1 AND ld.ESTATUS=11 and ld.remesa_id is not null 
and d.fecha_cierre_diario >= to_date('2016-12-05 00:00:00','yyyy-mm-dd HH24:MI:SS') 
and d.fecha_cierre_diario <= to_date('2016-12-05 23:59:00','yyyy-mm-dd HH24:MI:SS')
and d.plaza_id = '37'
GROUP BY D.COLABORADOR_ID,ld.diario_id ORDER BY D.COLABORADOR_ID DESC;

This query throws me the information row by row. 

DIARIO_ID   COLABORADOR_ID
27683       111250
28177       111250
28178       110493
28149       109260
27830       108978
27863       108978
27465       108698
27389       108698

What I'm looking for is that I generate it: 

   DIARIO_ID    COLABORADOR_ID
   27683,28177  111250
   28178        110493
   28149        109260
   27830,27863  108978
   27465,27389  108698

So that I send something similar to an "array" and process it in turn in a second query.

I'm waiting for comments.

For your attention.

Thank you.

    
asked by Jairo Ordaz Moreno 16.12.2016 в 16:46
source

2 answers

1

Reading the documentation and examples found LISTAGG 

select LISTAGG(diaro_id, ','),COLABORADOR_ID from XXQP.XXQP_DIARIO_LN_TBL ld inner join XXQP.XXQP_DIARIO_HD_TBL d on ld.diario_id=d.diario_id
WHERE 1 = 1 AND ld.ESTATUS=11 and ld.remesa_id is not null 
and d.fecha_cierre_diario >= to_date('2016-12-05 00:00:00','yyyy-mm-dd HH24:MI:SS') 
and d.fecha_cierre_diario <= to_date('2016-12-05 23:59:00','yyyy-mm-dd HH24:MI:SS')
and d.plaza_id = '37'
GROUP BY D.COLABORADOR_ID,ld.diario_id ORDER BY D.COLABORADOR_ID DESC

Link examples

Documentation

    
answered by 16.12.2016 в 17:16
0

I found the solution, I removed a table that was not very useful and my query was as follows: 

SELECT LISTAGG(DIARIO_ID,',') WITHIN GROUP (ORDER BY DIARIO_ID DESC) "DIARIO_ID"
FROM XXQP.XXQP_DIARIO_HD_TBL
WHERE  fecha_cierre_diario >= to_date('2016-12-05 00:00:00','yyyy-mm-dd HH24:MI:SS') 
and fecha_cierre_diario <= to_date('2016-12-05 23:59:00','yyyy-mm-dd HH24:MI:SS')
and plaza_id = '37'
GROUP BY COLABORADOR_ID;

I thank everyone for their support.

Without you it would not have been possible.

Greetings.

    
answered by 16.12.2016 в 18:24
Get the length of the variable stored in the database table with codeigniter Script to test the correct functioning of Fopen and Curl