Forum Stats

  • 3,851,709 Users
  • 2,264,014 Discussions
  • 7,904,821 Comments

Discussions

PORBLEMA CON PIVOT ANIDADO

Javier Rincon
Javier Rincon Member Posts: 5
edited Feb 26, 2016 5:13PM in SQL & PL/SQL

Buen día a todos, primero quiero informarles que este es mi primer post o pregunta en foros de oracle, sin embargo espero que todas las recomendaciones que me puedan hacer respecto a como debo hacer las preguntas de forma correcta, seran bien recibidas.

Bien como lo menciono en el titulo estoy haciendo un pivot anidado de una misma tabla, en donde necesito saber la cantidad (1 hora, 1 dia, un porcentaje %) y el valor pagado bajo un determinado criterio ( de 1 a n criterios) para cada uno de los empleados.

DATOS EJEMPLO

documento_empleadocriteriocantidadvalor
1010101110200
10101015151000

Sin embargo para solo algunos criterios necesito la cantidad y el valor, y para otros solo el valor. Por ese motivo decidí efectuar el siguiente query que se ejecuta sin ningún error. 

QUERY

SELECT h1.*,

              h2.*

FROM (

      SELECT *

        FROM (

        (SELECT documento_empleado,

                       criterio,

                       valor,

                       cantidad

            FROM tabla_pagos

          WHERE documento_empleado = 1010101

               AND empresa = 'EMPRESA 1'

               AND proceso = 1

               AND radicacion = 515253

               AND trunc(fecha) >= trunc(SYSDATE-60)

          )

          pivot (sum(valor) val, sum(cantidad) cant  FOR criterio IN (1 AS "SUELDO"))

          )

         )h1,

        ( SELECT *

            FROM (

            (SELECT documento_empleado,

                           criterio,

                           valor

               FROM tabla_pagos

            WHERE documento_empleado = 101010 AND

                 AND empresa = 'EMPRESA 1'

                 AND proceso = 1

                 AND radicacion = 515253

                 AND trunc(fecha) >= trunc(SYSDATE-60)

          ORDER BY documento_empleado desc

          )

        pivot (SUM(valor) FOR criterio IN (51 AS "DESCUENTO PENSION"))

        )

       ) h2

WHERE h1.documento_empleado = h2.documento_empleado;

Pero tengo el problema que la columna documento_empleado se repite, y no se como hacer un except de esa columna, teniendo en cuenta que debo hacer la relación (WHERE) para que los datos sean consistentes. ¿Alguna idea de como puedo hacer este query correctamente?.

Agradezco a quien me pueda ayudar.

*** Moderator action (Timo): User, this is an English language forum. Please Post your question in English language. Translation by Google follows ***

TRANSLATION:

Good day to all , first I want to inform you that this is my first post or ask in forums oracle , however I hope that all the recommendations that can make me regarding how I do the questions correctly, will be well received.

Well as I mentioned in the title I'm doing a nested one pivot table , where I need to know the amount (1 hour, 1 day , a percentage %) and the amount paid under a certain criterion ( 1 to n criteria) for each one of the employees .

DATOS EJEMPLO

documento_empleadocriteriocantidadvalor
1010101110200
10101015151000

However for some criteria I need only the quantity and value , and for others only the value. For this reason I decided to make the following query that runs without any errors.

QUERY

SELECT h1.*,

              h2.*

FROM (

      SELECT *

        FROM (

        (SELECT documento_empleado,

                       criterio,

                       valor,

                       cantidad

            FROM tabla_pagos

          WHERE documento_empleado = 1010101

               AND empresa = 'EMPRESA 1'

               AND proceso = 1

               AND radicacion = 515253

               AND trunc(fecha) >= trunc(SYSDATE-60)

          )

          pivot (sum(valor) val, sum(cantidad) cant  FOR criterio IN (1 AS "SUELDO"))

          )

         )h1,

        ( SELECT *

            FROM (

            (SELECT documento_empleado,

                           criterio,

                           valor

               FROM tabla_pagos

            WHERE documento_empleado = 101010 AND

                 AND empresa = 'EMPRESA 1'

                 AND proceso = 1

                 AND radicacion = 515253

                 AND trunc(fecha) >= trunc(SYSDATE-60)

          ORDER BY documento_empleado desc

          )

        pivot (SUM(valor) FOR criterio IN (51 AS "DESCUENTO PENSION"))

        )

       ) h2

WHERE h1.documento_empleado = h2.documento_empleado;


But I have the problem that the documento_empleado column is repeated , and not how to make a column except that , considering that I do the relationship ( WHERE) so that the data is consistent . Any idea how I can do this query correctly ?.

Thank who can help me .

Tagged:
Javier Rincon

Best Answer

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Feb 26, 2016 12:25PM Answer ✓

    Maybe


    with

    tabla_pagos as

    (select 1010101 documento_empleado,1 criterio,200 valor,10 cantidad from dual union all

    select 1010101,51,1000,5 from dual union all

    select 1010101,56,300,4 from dual union all

    select 1010101,315,800,1 from dual

    )

    select documento_empleado,criterio,nvl(v_val,b_val) val,nvl(v_cant,b_cant) cant

      from (select documento_empleado,criterio,valor,cantidad,case when criterio in (51,56) then 'both' else 'val' end use_type

              from tabla_pagos

           )

    pivot (sum(valor) val,sum(case use_type when 'both' then cantidad end) cant  for use_type IN ('val' as v,'both' as b))


    DOCUMENTO_EMPLEADOCRITERIOVALCANT
    1010101315800-
    10101015110005
    10101011200-
    1010101563004


    select documento_empleado,nvl(v_val,0) + nvl(b_val,0) val,nvl(v_cant,0) + nvl(b_cant,0) cant

      from (select documento_empleado,valor,cantidad,case when criterio in (51,56) then 'both' else 'val' end use_type

              from tabla_pagos

           )

    pivot (sum(valor) val,sum(case use_type when 'both' then cantidad end) cant  for use_type IN ('val' as v,'both' as b))


    DOCUMENTO_EMPLEADOVALCANT
    101010123009

    Regards


    Etbin

    Javier RinconJavier Rincon

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Feb 26, 2016 9:15AM
    select
        documento_empleado
      ,"Something_sum_of_Cant" AS "SUELDO"
      -- ,"Something_sum_of_valor" not_needed_1
      -- ,"Another_sum_of_Cant" not_needed_2
      ,"Another_sum_of_Valor" AS "DESCUENTO PENSION"
    from tabla_pagos
    pivot (
      sum(cantidad) "sum_of_Cant",
      sum(valor)    "sum_of_valor"
      FOR criterio
      in (1 AS "Something_", 51 AS "Another_" )
    )
    WHERE documento_empleado = 1010101
      AND empresa = 'EMPRESA 1'
      AND proceso = 1
      AND radicacion = 515253
      AND trunc(fecha) >= trunc(SYSDATE-60)
    

    The code is untested.

    I do not have access to 11g to test at this time.


    The aliases are concatenated to create the actual column name.

    I forgot which way they are concatenated.

    If this is not what you are seeking, please provide a "CREATE TABLE" statement, sample data, and more details on your problem.

    (please post in English)

    MK

  • Javier Rincon
    Javier Rincon Member Posts: 5
    edited Feb 26, 2016 11:06AM

    Hello, my english is very bad, but i'll try.


    data:


    CREATE TABLE tabla_pagos (documento_empleado NUMBER,

                              criterio NUMBER,

                              valor NUMBER,

                              cantidad NUMBER);

                             

    INSERT INTO  tabla_pagos (documento_empleado, criterio, valor, cantidad)

         VALUES (1010101, 1, 200, 10);                    

        

    INSERT INTO  tabla_pagos (documento_empleado, criterio, valor, cantidad)

         VALUES (1010101, 51, 1000, 5);                            

        

    INSERT INTO  tabla_pagos (documento_empleado, criterio, valor, cantidad)

         VALUES (1010101, 56, 300, 4);                            

        

    INSERT INTO  tabla_pagos (documento_empleado, criterio, valor, cantidad)

         VALUES (1010101, 315, 800, 1);                            

        

    COMMIT;

    QUERY

    SELECT h1.*,

           h2.*

    FROM (

          SELECT *

            FROM (

            (SELECT documento_empleado,

                           criterio,

                           valor,

                           cantidad

                FROM tabla_pagos

              WHERE documento_empleado = 1010101

              )

              pivot (sum(valor) val, sum(cantidad) cant  FOR criterio IN (1 AS "SUELDO"))

              )

             )h1,

            ( SELECT *

                FROM (

                (SELECT documento_empleado,

                         criterio,

                         valor

                   FROM tabla_pagos

                WHERE documento_empleado = 1010101

              )

            pivot (SUM(valor) FOR criterio IN (51 AS "DESCUENTO PENSION"))

            )

           ) h2

    WHERE h1.documento_empleado = h2.documento_empleado;

    Necessary for criterio = 1 know the value and quantity, however for the criterio = 51 it is necessary to know the value only for this reason that way the query was made, since they can sar of 1 to n criteria; however the documento_empleado appears twice (documento_empleado - documento_empleado_1, you will see when you run the query). I require that only appears once since this is a report to be migrated to excel and need delete the duplicate column.

  • CarlosDLG
    CarlosDLG Member Posts: 1,378 Gold Trophy
    edited Feb 26, 2016 11:25AM

    Can you post the results you should get from the test data you provided, and explain the logic behind wanting those results?

    Also, your query inlcudes only criterios 1 and 51.  What about the others?

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Feb 26, 2016 11:59AM
    d0eeb57d-93ec-4a24-b526-3f96fe3d48e1 wrote:
    
    however for the criterio = 51 it is necessary to know the value only for this reason that way the query was made,
    
    
    

    I think I understand. Can you provide more examples?

     I require that only appears once since this is a report to be migrated to excel and need delete the duplicate column.
    
    
    

    Do not use "SELECT H1.*, H2.*".

    Instead, SELECT only the columns you need.

    SELECT H1.documento_empleado, H1.SUELDOVAL, H1.SUELDOCANT, H2."DUSCUENTO PENSION"
    ....
    
    

    MK

    Javier Rincon
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Feb 26, 2016 12:25PM Answer ✓

    Maybe


    with

    tabla_pagos as

    (select 1010101 documento_empleado,1 criterio,200 valor,10 cantidad from dual union all

    select 1010101,51,1000,5 from dual union all

    select 1010101,56,300,4 from dual union all

    select 1010101,315,800,1 from dual

    )

    select documento_empleado,criterio,nvl(v_val,b_val) val,nvl(v_cant,b_cant) cant

      from (select documento_empleado,criterio,valor,cantidad,case when criterio in (51,56) then 'both' else 'val' end use_type

              from tabla_pagos

           )

    pivot (sum(valor) val,sum(case use_type when 'both' then cantidad end) cant  for use_type IN ('val' as v,'both' as b))


    DOCUMENTO_EMPLEADOCRITERIOVALCANT
    1010101315800-
    10101015110005
    10101011200-
    1010101563004


    select documento_empleado,nvl(v_val,0) + nvl(b_val,0) val,nvl(v_cant,0) + nvl(b_cant,0) cant

      from (select documento_empleado,valor,cantidad,case when criterio in (51,56) then 'both' else 'val' end use_type

              from tabla_pagos

           )

    pivot (sum(valor) val,sum(case use_type when 'both' then cantidad end) cant  for use_type IN ('val' as v,'both' as b))


    DOCUMENTO_EMPLEADOVALCANT
    101010123009

    Regards


    Etbin

    Javier RinconJavier Rincon
  • Javier Rincon
    Javier Rincon Member Posts: 5
    edited Feb 26, 2016 3:14PM

    Exactly, you have all the reason I want is a report with this structure.

    1. SELECT H1.documento_empleado, H1.SUELDOVAL, H1.SUELDOCANT, H2."DUSCUENTO PENSION" 
    2. .... 
    SELECT H1.documento_empleado, H1.SUELDOVAL, H1.SUELDOCANT, H2."DUSCUENTO PENSION"
    ....
    
    

    However, since the criteria (criteria field) can be 1 to N, ie they are many, would have the need to write one to one h1 values. or h2. and that would not be good for the fields are atypical. The example that I put, is a very, very simple example to get an idea of how to remove the column that I do not need.

    Can you think of another way to do this query?

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Feb 26, 2016 5:13PM

    The column list from a SELECT statement MUST BE STATIC !!

    Your requirement may not be clear.

    Please give an example were "criteria field" is 10.

    Thanks,

    MK

This discussion has been closed.