Conversion and grouping of rows in columns dynamically in Oracle

Question

Conversion and grouping of rows in columns dynamically in Oracle

Navigation

#1 by (2 votes)

3

Context:

I have a Project with several Versions of applications that will be developed, each Version several Requirement of New Functions (NF), each NF with several Codes changes in various Repositories. In addition, these NFs may contain changes in the same repositories.

Problem:

The query 1 I do in the database is bringing the NF Version and Repository, however I need to map the impact of and cross information to know the impact of one version on another, and an NF in another NF or Version, as Figure 1 .

Query 1

SELECT DISTINCT versao
               ,nf
               ,repositorio
          FROM tabeladoprojeto
         WHERE projeto = 1

Result - Query 1:

VERSAO      NF      REPOSITORIO
VER.D.1.0.0 56438   REPOSITORIO_G
VER.D.1.0.0 56438   REPOSITORIO_D
VER.F.1.0.0 56976   REPOSITORIO_F
VER.F.1.0.0 56976   REPOSITORIO_G
VER.F.1.0.0 56976   REPOSITORIO_B
VER.D.1.0.0 57049   REPOSITORIO_H
VER.D.1.0.0 57049   REPOSITORIO_E
VER.D.1.0.0 57049   REPOSITORIO_D
VER.D.1.0.0 57054   REPOSITORIO_H
VER.D.1.0.0 57054   REPOSITORIO_E
VER.D.1.0.0 57054   REPOSITORIO_D
VER.D.1.0.0 57054   REPOSITORIO_J
VER.D.1.0.0 57054   REPOSITORIO_A
VER.D.1.0.0 57056   REPOSITORIO_C
VER.D.1.0.0 57056   REPOSITORIO_E
VER.D.1.0.0 57056   REPOSITORIO_F
VER.D.1.0.0 57056   REPOSITORIO_H
VER.D.1.0.0 57157   REPOSITORIO_E
VER.D.1.0.0 57157   REPOSITORIO_A
VER.C.1.0.0 57892   REPOSITORIO_A
VER.C.1.0.0 57892   REPOSITORIO_B
VER.F.1.0.0 57942   REPOSITORIO_E
VER.F.1.0.0 57942   REPOSITORIO_G
VER.F.1.0.0 57942   REPOSITORIO_F
VER.F.1.0.0 58256   REPOSITORIO_F
VER.D.1.0.0 58447   REPOSITORIO_E
VER.D.1.0.0 58447   REPOSITORIO_D
VER.D.1.0.0 58576   REPOSITORIO_I
VER.D.1.0.0 58576   REPOSITORIO_E
VER.D.1.0.0 58576   REPOSITORIO_D
VER.C.1.0.0 58951   REPOSITORIO_I
VER.C.1.0.0 58951   REPOSITORIO_C
VER.C.1.0.0 58951   REPOSITORIO_B
VER.C.1.0.0 58951   REPOSITORIO_E

Figure 1: Figure 1 - Represents Versions with several NF and several changes in different Repositories, number 1 indicates change.

Need:

As Query 2 below, I use pivot to do this,   but it is not dynamic, that is, I have to inform what are the   columns, which will be transformed into rows and grouped together. This   I do a lot of work to do it manually, because what I went through was   real context is much more complex, being able to have 30 NF, and   in 40 repositories.

Thinking Query 1 I have the necessary repositories, I wanted   if it were possible to dynamically perform the query 2 return,   as below.

Query 2:

SELECT *
  FROM (SELECT DISTINCT versao
                   ,nf
                   ,repositorio
              FROM tabeladoprojeto
             WHERE projeto = 1) 
pivot
(
 COUNT(repositorio) FOR repositorio IN
 ('REPOSITORIO_G','REPOSITORIO_D','REPOSITORIO_F','REPOSITORIO_B','REPOSITORIO_H','REPOSITORIO_E','REPOSITORIO_J','REPOSITORIO_A','REPOSITORIO_C','REPOSITORIO_I')
)

Result - Query 2:

VER.C.1.0.0 57892   1   1   0   0   0   0   0   0   0   0  
VER.C.1.0.0 58951   0   1   0   0   0   0   0   0   0   0  
VER.D.1.0.0 57049   0   0   1   0   1   1   0   0   0   0    
VER.D.1.0.0 57157   1   0   0   0   1   0   0   0   0   0  
VER.D.1.0.0 58447   0   0   1   0   1   0   0   0   0   0  
VER.D.1.0.0 57056   0   0   0   0   1   0   0   0   1   1  
VER.D.1.0.0 56438   0   0   1   1   0   0   0   0   0   0  
VER.D.1.0.0 57054   1   0   1   0   1   0   1   1   0   0  
VER.F.1.0.0 57942   0   0   0   0   0   0   0   0   1   0  
VER.F.1.0.0 56976   0   1   0   1   0   0   0   0   1   0  
VER.F.1.0.0 58256   0   0   0   0   0   0   0   0   1   0

The above data are examples that may not agree with each other.

Thank you very much

oracle pl-sql pivot

asked by anonymous 18.12.2015 / 21:08

1 answer

em.createNativeQuery executes first than em.persist and now? jQuery resizable () does not drag panel-footer

score 2 · Accepted Answer

According to the Fernando AW comment on an answer in the [so] of the question Dynamic pivot in oracle sql of User . Help in parts, let's explain.

Conforms select below, is executed to generate the necessary columns for pivot to do the conversion of rows into columns, thus improving the process, but not solving effectively.

SELECT DISTINCT listagg('''' || repositorio || ''' AS ' || repositorio,',') within GROUP(ORDER BY repositorio) AS temp_in_statement
  FROM (SELECT DISTINCT repositorio
          FROM (SELECT repositorio
                  FROM tabeladoprojeto
                 WHERE projeto = 1))

The return is something like this:

'REPOSITORIO_A' as REPOSITORIO_A,'REPOSITORIO_B' as REPOSITORIO_B,'REPOSITORIO_C' as REPOSITORIO_C

The final select passing the return input above:

SELECT *
  FROM (SELECT DISTINCT versao
                   ,nf
                   ,repositorio
              FROM tabeladoprojeto
             WHERE projeto = 1) 
pivot
(
 COUNT(repositorio) FOR repositorio IN
 (&temp_in_statement)
)

Although it has 4000 bytes limitation to concatenate a sequence, in my tests it covers all repositories.