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!

Find unique and sorted field value

Discussion in 'SQL PL/SQL' started by amarbose, Aug 16, 2016.

  1. amarbose

    amarbose Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    A column named input contains the following values in a table.The column Transformed output contains unique value of the column named input and in sorted manner.

    Sample output is present in column Transformed Output.

    Code (SQL):
    INPUT                                                     Transformed output
    G25,G25,G25,G25,G28,G28,G29,G25,G25                             G25,G28,G29
    G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26                     G25,G26,G28,G29
    How to achieve this by writing a single sql query
     
  2. Silambarasan K

    Silambarasan K Active Member

    Messages:
    6
    Likes Received:
    3
    Trophy Points:
    85
    Location:
    Chennai
    Hi Amarbose ,
    i Dont know we can do your requirement in single SQL code. But i tried and find solution for this through PLSQL . If your data length must be three then u can use this code . i,e) length(G25)=3;

    -- given input data insert into table :
    create table temp_test_new
    (
    input varchar2(500)
    );

    insert into temp_test_new values('G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26');
    commit;
    --- create alter table to insert rows into columns:
    create table temp_test_sxkn
    (
    input_key varchar2(60),
    input varchar2(10)
    );

    truncate table temp_test_sxkn ;

    -- query for convert rows data into values :
    declare
    i_text varchar2(50);
    i_rowid varchar2(150);
    out_text varchar2(50);
    s number(3) :=0;
    j number(5) :=1;
    cursor c1 is
    select input,r_n
    from (select replace(input,',') as input,rowid as r_n
    from temp_test_new)inl ;

    begin
    if c1%isopen
    then
    close c1 ;
    end if;

    open c1 ;
    loop
    fetch c1 into i_text,i_rowid ;
    if s < c1%rowcount
    then
    j:=1;
    else
    j:=j+3;
    end if;
    for i in 1..(length(i_text)/3) loop
    out_text:= substr(i_text,j,3);
    -- dbms_output.put_line(out_text);
    -- dbms_output.put_line(s||' '||j||' '||out_text);
    j:=j+3;
    insert into temp_test_sxkn values(i_rowid,out_text);
    exit when i>(length(i_text)/3);
    end loop;
    s:= c1%rowcount;
    exit when c1%notfound;
    dbms_output.put_line(c1%rowcount||'sialmbu') ;
    end loop;
    close c1;
    end;

    --- to select inserted values :
    select *
    from temp_test_sxkn ;
    -- to select tanformed_out by wm_concat :

    select distinct wm_concat(distinct input) over(partition by input_key) as transformed_output
    from temp_test_sxkn ;


    I think we can able to do this by Unpivot function oracle 11G feature.


    Thanks and regards,
    silambu K
     
  3. Silambarasan K

    Silambarasan K Active Member

    Messages:
    6
    Likes Received:
    3
    Trophy Points:
    85
    Location:
    Chennai
    Apologies to previous complexity , now i have reduce complex by below query

    select distinct wm_concat(distinct input) over(partition by r_n) as tranformed_output
    from (
    SELECT SUBSTR (REPLACE(input,',') , (3 * LEVEL)-2,3) as input ,rowid as r_n FROM temp_test_new
    CONNECT BY LEVEL <= (LENGTH (REPLACE(input,',')) /3)
    ) inl

    i insert your input into table such that temp_test_new .

    i think this is very useful to you ..,

    Thanks and regards,
    Silambarasan k