Forum Stats

  • 3,853,209 Users
  • 2,264,192 Discussions
  • 7,905,287 Comments

Discussions

[PIVOT] combine pivot column with other?

TPD-Opitz
TPD-Opitz Member Posts: 2,465 Silver Trophy
edited Feb 18, 2015 7:33AM in SQL & PL/SQL

Hello.

I have this statement in my 11g database:

WITH my_data AS
       (    SELECT    'pave'
                   || MOD( LEVEL
                         , 2
                          )
                     AS pave_name
                 , MOD( LEVEL
                      , 2
                       )
                     AS pave_version_nr
                 ,    'Cogr'
                   || FLOOR(   (  1
                                + LEVEL )
                             / 2
                            )
                     cogr_name
                 , LEVEL AS value_init
                 , 2 AS value_delivery
                 , 'fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa' AS coin_names
              FROM DUAL
        CONNECT BY LEVEL < 9)
  SELECT *
    FROM (SELECT ROW_NUMBER( )
                   OVER( PARTITION BY cogr_name
                         ORDER BY
                           pave_name
                         , pave_version_nr
                        )
                   AS set_id
               , cg.*
            FROM my_data cg                                                                                                               --
                           )
ORDER BY cogr_name;

resulting in this output:

    SET_ID PAVE_NAME  PAVE_VERSION_NR COGR_NAME VALUE_INIT VALUE_DELIVERY COIN_NAMES

---------- ---------- --------------- --------- ---------- -------------- -----------------------------------------

         1 pave0                    0 Cogr1              2              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

         2 pave1                    1 Cogr1              1              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

         1 pave0                    0 Cogr2              4              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

         2 pave1                    1 Cogr2              3              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

         1 pave0                    0 Cogr3              6              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

         2 pave1                    1 Cogr3              5              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

         1 pave0                    0 Cogr4              8              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

         2 pave1                    1 Cogr4              7              2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

For each distinct COGR_NAME I need to compare VALUE_INIT, VALUE_DELIVERY and COIN_NAMES.

so my desired output is this:

PAVE_NAME_1 PAVE_VERSION_NR_1 COGR_NAME_1 VALUE_INIT_1 VALUE_DELIVERY_1 COIN_NAMES_1                              PAVE_NAME_2 PAVE_VERSION_NR_2 COGR_NAME_2 VALUE_INIT_2 VALUE_DELIVERY_2 COIN_NAMES_2

----------- ----------------- ----------- ------------ ---------------- ----------------------------------------- ----------- ----------------- ----------- ------------ ---------------- -----------------------------------------

pave0                       0 Cogr1                  2                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa pave1                       1 Cogr1                  1                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

pave0                       0 Cogr2                  4                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa pave1                       1 Cogr2                  3                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

pave0                       0 Cogr3                  6                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa pave1                       1 Cogr3                  5                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

pave0                       0 Cogr4                  8                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa pave1                       1 Cogr4                  7                2 fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa

I was trying a solution that groups by COGR_NAME and uses max() keep(dense_rank last/first order by set_id) but this does not work with COIN_NAMES which is really a CLOB.

So I guess that's possible with pivoting the query but any other solution is also welcome...

bye

TPD

Tagged:
TPD-Opitz

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Feb 18, 2015 6:23AM Answer ✓

    Hi,

    One way is with a self-join, like this:

    WITH    got_analytics     AS
    (
        SELECT  d.*       -- or whatever columns you want
        ,       ROW_NUMBER () OVER ( PARTITION BY  cogr_name
                                     ORDER BY      pave_name
                                     ,             pave_version_nr
                                   )   AS set_id
        ,       COUNT (*)     OVER ( PARTITION BY  cogr_name
                                   )   AS n_rows
        FROM    my_data  d
    )
    SELECT    a1.*, a2.*  -- or whatever columns you want
    FROM      got_analytics  a1
    JOIN      got_analytics  a2  ON   a1.cogr_name  = a2.cogr_name
    WHERE     a1.set_id  = 1
    AND       a2.set_id  = a2.n_rows
    ORDER BY  a1.cogr_name
    ;
    
    TPD-OpitzTPD-Opitz

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Feb 18, 2015 6:23AM Answer ✓

    Hi,

    One way is with a self-join, like this:

    WITH    got_analytics     AS
    (
        SELECT  d.*       -- or whatever columns you want
        ,       ROW_NUMBER () OVER ( PARTITION BY  cogr_name
                                     ORDER BY      pave_name
                                     ,             pave_version_nr
                                   )   AS set_id
        ,       COUNT (*)     OVER ( PARTITION BY  cogr_name
                                   )   AS n_rows
        FROM    my_data  d
    )
    SELECT    a1.*, a2.*  -- or whatever columns you want
    FROM      got_analytics  a1
    JOIN      got_analytics  a2  ON   a1.cogr_name  = a2.cogr_name
    WHERE     a1.set_id  = 1
    AND       a2.set_id  = a2.n_rows
    ORDER BY  a1.cogr_name
    ;
    
    TPD-OpitzTPD-Opitz
  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Feb 18, 2015 7:29AM

    Thanks for the quick reply.

    Unfortunately is does not work. when doing the self join the clobs are empty : o(

    WITH my_data AS
           (    SELECT    'pave'
                       || MOD( LEVEL
                             , 2
                              )
                         AS pave_name
                     , MOD( LEVEL
                          , 2
                           )
                         AS pave_version_nr
                     ,    'Cogr'
                       || FLOOR(   (  1
                                    + LEVEL )
                                 / 2
                                )
                         cogr_name
                     , LEVEL AS value_init
                     , 2 AS value_delivery
    --                                  ,to_clob( 'fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa' )AS coin_names
                     , ( SELECT RTRIM( XMLAGG( XMLELEMENT( e
                                                         , id
                                                         , ','
                                                          ).EXTRACT( '//text()' ) ORDER BY id ).getclobval( )
                                     , ','
                                      )
                           FROM (    SELECT    'instance'
                                            || LEVEL
                                              AS id
                                       FROM DUAL
                                 CONNECT BY LEVEL < 1050) )
                         AS coin_names
                  FROM DUAL
            CONNECT BY LEVEL < 9)
      SELECT *
        FROM (SELECT *
                FROM (SELECT ROW_NUMBER( )
                               OVER( PARTITION BY cogr_name
                                     ORDER BY
                                       pave_name
                                     , pave_version_nr
                                    )
                               AS set_id
                           , cg.*
                           , length(coin_names)
                           , DBMS_CRYPTO.hash( NVL( coin_names, TO_CLOB( 'dummy' ) )
                                             , 1
                                              )
                               coin_names_hash
                        FROM my_data cg) set_1
    --                 JOIN (SELECT ROW_NUMBER( )
    --                                OVER( PARTITION BY cogr_name
    --                                      ORDER BY
    --                                        pave_name
    --                                      , pave_version_nr
    --                                     )
    --                                AS set_id
    --                            , cg.*
    --                       , length(coin_names)
    --                            , DBMS_CRYPTO.hash( NVL( coin_names, TO_CLOB( 'dummy' ) )
    --                                              , 1
    --                                               )
    --                                coin_names_hash
    --                         FROM my_data cg) set_2
    --                   ON ( set_1.cogr_name = set_2.cogr_name
    --                   AND set_1.set_id = 1
    --                   AND set_2.set_id = 2 )
                       )
    

    when uncommenting the second selection the clobs are NULL.

    bye

    TPD

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Feb 18, 2015 7:33AM

    moving the "self joined" sub query to the with clause solves this:

    WITH my_data AS
          (    SELECT    'pave'
                      || MOD( LEVEL
                            , 2
                              )
                        AS pave_name
                    , MOD( LEVEL
                          , 2
                          )
                        AS pave_version_nr
                    ,    'Cogr'
                      || FLOOR(  (  1
                                    + LEVEL )
                                / 2
                                )
                        cogr_name
                    , LEVEL AS value_init
                    , 2 AS value_delivery
    --                                  ,to_clob( 'fakjfhajhfkajöfdsgaösfdghöaoifdsgöafsghöa' )AS coin_names
                    , ( SELECT RTRIM( XMLAGG( XMLELEMENT( e
                                                        , id
                                                        , ','
                                                          ).EXTRACT( '//text()' ) ORDER BY id ).getclobval( )
                                    , ','
                                      )
                          FROM (    SELECT    'instance'
                                            || LEVEL
                                              AS id
                                      FROM DUAL
                                CONNECT BY LEVEL < 1050) )
                        AS coin_names
                  FROM DUAL
            CONNECT BY LEVEL < 9),
            aggregated as(SELECT ROW_NUMBER( )
                              OVER( PARTITION BY cogr_name
                                    ORDER BY
                                      pave_name
                                    , pave_version_nr
                                    )
                              AS set_id
                          , cg.*
                          , length(coin_names)
                          , DBMS_CRYPTO.hash( NVL( coin_names, TO_CLOB( 'dummy' ) )
                                            , 1
                                              )
                              coin_names_hash
                        FROM my_data cg)
      SELECT *
        FROM (SELECT *
                FROM aggregated set_1
                    JOIN aggregated set_2
                      ON ( set_1.cogr_name = set_2.cogr_name
                      AND set_1.set_id = 1
                      AND set_2.set_id = 2 )
                      );
    

    1    pave0    0    Cogr1    2    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905    2    pave1    1    Cogr1    1    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905

    1    pave0    0    Cogr2    4    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905    2    pave1    1    Cogr2    3    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905

    1    pave0    0    Cogr3    6    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905    2    pave1    1    Cogr3    5    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905

    1    pave0    0    Cogr4    8    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905    2    pave1    1    Cogr4    7    2    (HugeClob)    12529    3A2590A039C19180D550289644F77905

    bye

    TPD

    TPD-Opitz
This discussion has been closed.