Results 1 to 5 of 5

Thread: SQL Query

  1. #1
    Join Date
    Jul 2012
    Location
    Bangalore
    Posts
    8

    Unanswered: SQL Query

    Hey guys,
    I stuck with a sql query. I am not sure how to go about it.

    Let say there is a table 'tab_a' with two columns 'Col1' and 'Col2'
    Col1(VARCHAR2) contains data: 'ABCDE'
    Col2(NUMBER) contains data: '11222'

    i.e; select * from tab_a;
    return: COL1 COL2
    ------ ------
    ABCDE 11222

    Now, I want to write a query to get data like :
    COLUMN1 COLUMN2
    ----------- ----------
    1 A$B
    2 C$D$E

    I guess I am able to explain my doubt. Please help me with this.

    Thanks for your help in advance.

    Regards
    Abhinav

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How long Col1? In other word, VARCHAR2(nnn?).
    Col2 conains only '1' or '2'? Or more?
    If Col2 can conain '3' or more, are they contiguos, are '11133', '23121', '22333' possible?

  3. #3
    Join Date
    Jul 2012
    Location
    Bangalore
    Posts
    8

    SQL Query

    Col1 could be of size VARCHAR2(10) and COL2 is NUMBER.
    VALUE can vary in COL1 and COL2, like

    COL1 COL2
    ------- ----------
    ABCDEFA 1122334

    then result should be like
    COLUMN1 COLUMN2
    --------- ---------
    1 A$B
    2 C$D
    3 E$F
    4 A


    or

    COL1 COL2
    ---------- ----------
    ABCDEFAK 11223341

    then result should be like
    COLUMN1 COLUMN2
    --------- ---------
    1 A$B$K
    2 C$D
    3 E$F
    4 A

    I hope this example makes it clear for you.

    Thanks in Advance.

    Regards
    Abhinav

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one option:
    Code:
    SQL> select * from test;
    
    COL1             COL2
    ---------- ----------
    ABCDE           11222
    ABCDEFA       1122334
    ABCDEFAK     11223341
    
    SQL> with
      2  prva as
      3    (select
      4       t.col1,
      5       substr(t.col1, m.column_value, 1) sub_s,
      6       substr(t.col2, m.column_value, 1) sub_b
      7     from
      8       test t,
      9       table(cast(multiset(select level from dual
     10                           connect by level <= length(t.col2)
     11                          ) as sys.odcinumberlist)) m
     12    )
     13  select
     14    col1,
     15    sub_b,
     16    rtrim( xmlagg (xmlelement (e, sub_s || '$')).extract ('//text()'), '$') result
     17  from prva
     18  group by
     19    col1,
     20    sub_b;
    
    COL1       S RESULT
    ---------- - --------------------
    ABCDE      1 A$B
    ABCDE      2 C$E$D
    ABCDEFA    1 A$B
    ABCDEFA    2 C$D
    ABCDEFA    3 E$F
    ABCDEFA    4 A
    ABCDEFAK   1 A$B$K
    ABCDEFAK   2 C$D
    ABCDEFAK   3 E$F
    ABCDEFAK   4 A
    
    10 rows selected.
    
    SQL>

  5. #5
    Join Date
    Jul 2012
    Location
    Bangalore
    Posts
    8
    Yes.. This is exactly what i needed.
    Thanks for your help..

    Regards
    Abhinav

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •