1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Output from different columns needs to merged

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Feb 20, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,

    I have a output table with values as below



    P_K Col1 Col2 Col3

    1 A null null

    1 null B null

    1 null null C

    2 A2 null null

    2 null B2 null

    2 null null C2



    I need to get output as



    P_K Col1 Col2 Col3



    1 A B C

    2 A2 B2 C2

    I tried to do self join with not null like below

    select a.p_k, a.col1, b.col2, c.col3
    from t7 a, t7 b, t7 c
    where a.p_k= b.p_k
    and b.p_k= c.p_k
    and a.col1 is not null
    and b.col2 is not null
    and c.col3 is not null

    This works but is there any built in functionality or any other optimal way to get this output
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    May be helpful :Aggregate Functions
    For example:

    Code (SQL):
    WITH your_tab (P_K,Col1,Col2,Col3) AS  (
    SELECT 1, 'A', NULL, NULL  FROM dual UNION ALL

    SELECT 1 ,NULL, 'B', NULL FROM dual UNION ALL

    SELECT 1 ,NULL ,NULL, 'C'  FROM dual UNION ALL

    SELECT 2, 'A2' ,NULL, NULL FROM dual UNION ALL

    SELECT 2 ,NULL ,'B2', NULL FROM dual UNION ALL

    SELECT 2 ,NULL ,NULL, 'C2' FROM dual
    )

    SELECT
    p_k ,
    MAX(col1) col1,
    MAX(col2) col2,
    MAX(col3)  col3
    FROM your_tab
    GROUP BY p_k;
    Code (SQL):

    WITH your_tab (P_K,Col1,Col2,Col3) AS  (
    SELECT 1, 'A', 'B', NULL  FROM dual UNION ALL

    SELECT 1 ,NULL, 'A', NULL FROM dual UNION ALL

    SELECT 1 ,NULL ,NULL ,'C'  FROM dual UNION ALL

    SELECT 2, 'A2' ,NULL, NULL FROM dual UNION ALL

    SELECT 2 ,NULL ,'B2', NULL FROM dual UNION ALL

    SELECT 2 ,'C2' ,NULL, 'C2' FROM dual
    )

    SELECT
    p_k ,
    MAX(col1) keep(dense_rank FIRST ORDER BY col1 NULLS LAST) col1,
    MAX(col2) keep(dense_rank FIRST ORDER BY col2 NULLS LAST) col2,
    MAX(col3) keep(dense_rank FIRST ORDER BY col3 NULLS LAST) col3
    FROM your_tab
    GROUP BY p_k;

    --4compare
    WITH your_tab (P_K,Col1,Col2,Col3) AS  (
    SELECT 1, 'A', 'B', NULL  FROM dual UNION ALL

    SELECT 1 ,NULL, 'A', NULL FROM dual UNION ALL

    SELECT 1 ,NULL ,NULL ,'C'  FROM dual UNION ALL

    SELECT 2, 'A2' ,NULL, NULL FROM dual UNION ALL

    SELECT 2 ,NULL ,'B2', NULL FROM dual UNION ALL

    SELECT 2 ,'C2' ,NULL, 'C2' FROM dual
    )

    SELECT
    p_k ,
    MAX(col1) col1,
    MAX(col2) col2,
    MAX(col3)  col3
    FROM your_tab
    GROUP BY p_k;

     
     
    Last edited: Feb 20, 2017
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Thanks a lot for your help Sergey :) Just a quick question out of curiosity, can we use listagg in this case?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    If you want to get a list of items (string aggrigation), then you can use listagg...
    What you want to do ?


    for example and studies...listagg


    Code (SQL):
    WITH your_tab (P_K,Col1,Col2,Col3) AS  (
    SELECT 1, 'A', 'B', NULL  FROM dual UNION ALL

    SELECT 1 ,NULL, 'A', NULL FROM dual UNION ALL

    SELECT 1 ,NULL ,NULL ,'C'  FROM dual UNION ALL

    SELECT 2, 'A2' ,NULL, NULL FROM dual UNION ALL

    SELECT 2 ,NULL ,'B2', NULL FROM dual UNION ALL

    SELECT 2 ,'C2' ,NULL, 'C2' FROM dual
    )

    SELECT
    p_k ,
    LISTAGG(col1, ',')
             WITHIN GROUP (ORDER BY p_k)  col1,

    LISTAGG(col2, ',')
             WITHIN GROUP (ORDER BY p_k)  col2,
    LISTAGG(col3, ',')
             WITHIN GROUP (ORDER BY p_k)  col3    
    FROM your_tab
    GROUP BY p_k;