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!

Need help in SQL

Discussion in 'SQL PL/SQL' started by venkateshguru, Nov 18, 2013.

  1. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Hi Guru's,

    i need a write a query and output should be as below . Please help me out.

    Output:
    1,2,3

    Thanks,
    Venkatesh.G
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    What is your oracle version? (with 4 digits)
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    1)
    example :

    Code (SQL):

    WITH
    sample_data
    AS
    (
        SELECT --+ no_unnest
            MOD(level,10) part , level val  
        FROM dual
        CONNECT BY level < 101
    )
    SELECT
        sd.part,
        sys_xmlagg(xmlelement(part,VAL||',')).EXTRACT('/ROWSET/PART/text()').getStringVal() val
    FROM sample_data sd
    GROUP BY sd.part;

    SQL>
     
          PART VAL
    ---------- --------------------------------------------------------------------------------
             0 10,100,90,80,70,60,50,40,30,20,
             1 1,91,81,71,61,51,41,31,21,11,
             2 2,92,82,72,62,52,42,32,22,12,
             3 3,93,83,73,63,53,43,33,23,13,
             4 4,94,84,74,64,54,44,34,24,14,
             5 5,95,85,75,65,55,45,35,25,15,
             6 6,96,86,76,66,56,46,36,26,16,
             7 7,97,87,77,67,57,47,37,27,17,
             8 8,98,88,78,68,58,48,38,28,18,
             9 9,99,89,79,69,59,49,39,29,19,
     
    10 ROWS selected



     

    2)
    example for 11g :

    Code (SQL):


    WITH
    sample_data
    AS
    (
        SELECT  --+ no_unnest
            MOD(level,10) part , level val  
        FROM dual
        CONNECT BY level < 101
    )
    SELECT
        sd.part,
        listagg (sd.val,',')  WITHIN GROUP (ORDER BY part) list_val
    FROM sample_data sd
    GROUP BY sd.part

    SQL>
     
          PART LIST_VAL
    ---------- --------------------------------------------------------------------------------
             0 10,100,20,30,40,50,60,70,80,90
             1 1,11,21,31,41,51,61,71,81,91
             2 12,2,22,32,42,52,62,72,82,92
             3 13,23,3,33,43,53,63,73,83,93
             4 14,24,34,4,44,54,64,74,84,94
             5 15,25,35,45,5,55,65,75,85,95
             6 16,26,36,46,56,6,66,76,86,96
             7 17,27,37,47,57,67,7,77,87,97
             8 18,28,38,48,58,68,78,8,88,98
             9 19,29,39,49,59,69,79,89,9,99
     
    10 ROWS selected


     


    3) example only for education :)

    P.S. as of version 10.2.0.5 the result return as CLOB type



    Code (SQL):


    WITH
    sample_data
    AS
    (
        SELECT --+ no_unnest
            MOD(level,10) part , level val  
        FROM dual
        CONNECT BY level < 101
    )
    SELECT
        sd.part,
       wm_concat(sd.val) val
    FROM sample_data sd
    GROUP BY sd.part;

    SQL>
     
          PART VAL
    ---------- --------------------------------------------------------------------------------
             0 10,100,90,80,70,60,50,40,30,20
             1 1,91,81,71,61,51,41,31,21,11
             2 2,92,82,72,62,52,42,32,22,12
             3 3,93,83,73,63,53,43,33,23,13
             4 4,94,84,74,64,54,44,34,24,14
             5 5,95,85,75,65,55,45,35,25,15
             6 6,96,86,76,66,56,46,36,26,16
             7 7,97,87,77,67,57,47,37,27,17
             8 8,98,88,78,68,58,48,38,28,18
             9 9,99,89,79,69,59,49,39,29,19
     
    10 ROWS selected
     
     
    venkateshguru likes this.
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Interesting examples but they don't actually answer the question and we do not do someone elses homework for them. Providing such examples absent any proof of work by the original poster only serves to 'spoon feed' solutions without providing any real understanding. We simply don't do that here.

    The original post states:

    "
    Hi Guru's,

    i need a write a query and output should be as below . Please help me out.

    Output:
    1,2,3

    Thanks,
    Venkatesh.G "

    There are no other specifications, no table definitions, no sample data, no additional information from the assigned problem. Any number of 'solutions' present themselves, some version dependent.

    Until the original poster provides more information and proof that he or she has actually attempted the problem and failed solutions should not be presented, even as 'examples'.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Ok....

    My students asked questions of the similar plan: how to obtain data in one line...
    I by analogy also gave examples.

    Such problem definition as this "i need a write a query and output should be as below. Please help me out.
    Output:
    1,2,3"
    isn't a problem definition....
     
    venkateshguru likes this.
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it's a poorly veiled attempt to get someone else to do the original poster's work, in my opinion. Such attempts should be met with responses similar to 'please show what you have done to solve this', and no examples should be provided until such work is posted.

    If YOU are the teacher and YOU are addressing YOUR students such responses are expected; when you are addressing those who are NOT your students by providing them solutions to problems NOT assigned by you that, in my opinion, and in the general consensus of the forum membership, is simply 'spoon feeding' and teaches nothing, which is why it is the policy of this forum to not provide such examples without proof from the original poster that some attempt was made to solve the 'problem' on their own.
     
  8. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Hi David/Team,

    First of all sorry for providing less information. Actually it is interview question asked me to write a query and output as 1,2,3. I found the answer and below is the query.
    with t as
    (
    SELECT 1 n1, 1 n2 FROM DUAL
    UNION ALL
    SELECT 1 n1, 2 n2 FROM DUAL
    UNION ALL
    SELECT 1 n1, 3 n2 FROM DUAL
    )
    select x from (select LISTAGG(n2,',') within group(order by null) x from t group by n1 )

    Let me know if any other way is there to achieve the same output.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is obviously MORE to the question than you have chosen to reveal as the simplest solution is:

    Code (SQL):

    SELECT '1, 2, 3'
    FROM dual;
     
    There are other ways, using CONNECT BY and LEVEL, along with the 'solution' you provided. Since you didn't provide the full interview question I won't go any further with examples.