Conversion and Grouping of Rows into columns dynamically in Oracle

Asked

Viewed 7,165 times

3

Context:

I have a Project mixed Versions of apps that will be developed, each various Version New Feature Requirement (NF) means each NF with several Code changes in several Repositories. In addition, these Nfs may contain changes to the same repositories.

Problem:

To query 1 that I do at the bank 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 of an NF in another NF or Version, as per Figure 1.

Consultation 1

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

Result - Consultation 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: Versões com varias NF e varias alterações em Repositórios diferentes Figure 1 - Represents Versions with several NF and several changes in different Repositories, the number 1 indicates change.

Need:

In accordance with Query 2 below, I use the pivotto do this, but it is not dynamic, I mean, I have to say what are the columns, which will be transformed into rows and grouped. This work to do manually, because what I went through was example, in the real context is much more complex, can have 30 NF, and changes in 40 repositories.

Thinking that Consultation 1 I have the necessary repositories, I wanted if it were possible to dynamically return consultation 2, 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 - Consultation 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
  • 1

    David.. Something like this wouldn’t solve your problem? Dynamic-pivot-in-oracle-sql. I would leave the repositories in a dynamic way. If I understood the problem correctly.

  • 1

    @Fernandoa.W., I performed the tests, the first answer that is marked as solved, already knew, but I can not work with XML. The third answer tested and did not work, in the article reported by the author faces the same error as people. Already the second answer of User meets in parts, it generates the list of Reposorios more quickly, helps, but does not resolve effectively, thank you very much!

  • 1

    Perfect, good that helped you.

1 answer

2


As commented by Fernando A.W 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, runs to generate the columns needed for the pivot converting rows into columns, thus improving the process, but not effectively solving.

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 entry above:

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

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

Browser other questions tagged oracle pl-sql pivot

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