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!

sql query for this requirement

Discussion in 'SQL PL/SQL' started by laxman, Apr 26, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi All,
    I have one small requirement where i need output in format mentioned below.

    I have table T

    Code (SQL):
    sql> SELECT * FROM T;

    PRSID       X          Y          Z
    100          2           1           1
    100          1           0           1
    100          4           1           0
    200          2           0           1
    200          3           0           1
    Now if i need output in following format is it possible!!!
    100 (2,1,1),(1,0,1),(4,1,0)
    200 (2,0,1),(3,0,1)

    Thanks n regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, learn to use the contatenation operator (||) to 'string' together column values; you'll also need PL/SQL to finish the job. I do not normally do people's work for them but given your level of knowledge it's the best way to explain this:

    Code (SQL):
     
    SQL> CREATE TABLE t(prsid NUMBER,
    2 x NUMBER,
    3 y NUMBER,
    4 z NUMBER)
    5 tablespace tools;
    TABLE created.
    SQL>
    SQL> INSERT ALL
    2 INTO t
    3 VALUES (100,2,1,1)
    4 INTO t
    5 VALUES (100,1,0,1)
    6 INTO t
    7 VALUES (100,4,1,0)
    8 INTO t
    9 VALUES (200,2,0,1)
    10 INTO t
    11 VALUES (200,3,0,1)
    12 SELECT * FROM dual;
    5 ROWS created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> DECLARE
    2 cursor get_concat_vals IS
    3 SELECT prsid, '('||x||','||y||','||z||')' convals
    4 FROM t
    5 ORDER BY prsid;
    6
    7 prev_prsid NUMBER:=NULL;
    8 data_string varchar2(200);
    9 BEGIN
    10 FOR trec IN get_concat_vals loop
    11 IF prev_prsid IS NULL THEN
    12 data_string:=trec.prsid||' '||trec.convals;
    13 prev_prsid := trec.prsid;
    14 elsif prev_prsid = trec.prsid THEN
    15 data_string := data_string||','||trec.convals;
    16 ELSE
    17 dbms_output.put_line(data_string);
    18 data_string:=trec.prsid||' '||trec.convals;
    19 prev_prsid := trec.prsid;
    20 END IF;
    21 END loop;
    22 dbms_output.put_line(data_string);
    23 END;
    24 /
    100 (2,1,1),(1,0,1),(4,1,0)
    200 (2,0,1),(3,0,1)
    PL/SQL PROCEDURE successfully completed.
    SQL>
     
     
    If you're using 11.2.0 you can also do this with the listagg function:

    Code (SQL):
     
    SELECT tc.prsid, listagg(tc.convals ',') WITHIN GROUP (ORDER BY prsid) val_list
    FROM
    (SELECT prsid, '('||x||','||y||','||z||')' convals
    FROM t
    ORDER BY prsid) tc;
     
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear Sir,
    i am speechless!!!, a heartfelt thanks to you and truly a mind-blowing approach . Hats off to you Sir.

    Regards
    Laxman
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Might be A typo you need to add Group by to Zargon's solution.

    The Following query will work in Oracle 9i and 10g. It uses Sys_Connect_By_Path.

    Code (SQL):
    SQL>  SELECT * FROM T;

         PRSID          X          Y          Z
    ---------- ---------- ---------- ----------
           100          2          1          1
           100          1          0          1
           100          4          1          0
           200          2          0          1
           200          3          0          1

    SQL>
    SQL>   SELECT PRSID ,
      2           REPLACE(SUBSTR(MAX ( sys_connect_by_path( concatval, ', ' )),2),' ')  CONCATVAL
      3     FROM ( SELECT PRSID,
      4                  ( '('||X||','|| Y||','||Z ||')') CONCATVAL,
      5                  ROW_NUMBER() OVER (PARTITION BY PRSID ORDER BY ROWNUM)  RN
      6             FROM T )
      7    START WITH RN=1
      8  CONNECT BY  RN = PRIOR RN+1
      9      AND PRSID = PRIOR PRSID
     10    GROUP BY PRSID ;

         PRSID CONCATVAL
    ---------- --------------------------------------------------
           100 (2,1,1),(1,0,1),(4,1,0)
           200 (2,0,1),(3,0,1)

    SQL>
     
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And if the maximum number of record is fixed (say 5) the we can get the result in Oracle 8i too.

    Code (SQL):
    SQL> SELECT PRSID,
      2                TRIM ( BOTH ',' FROM   MAX(DECODE(rn,1, CONCATVAL))||','||
      3                                       MAX(DECODE(rn,2, CONCATVAL))||','||
      4                                       MAX(DECODE(rn,3, CONCATVAL))||','||
      5                                       MAX(DECODE(rn,4, CONCATVAL))||','||
      6                                       MAX(DECODE(rn,5, CONCATVAL))  ) CONCATVAL
      7   FROM ( SELECT PRSID,
      8                ( '('||X||','|| Y||','||Z ||')') CONCATVAL,
      9                ROW_NUMBER() OVER (PARTITION BY PRSID ORDER BY ROWNUM)  RN
     10           FROM T )
     11   GROUP BY PRSID;

         PRSID CONCATVAL
    ---------- --------------------------------------------------
           100 (2,1,1),(1,0,1),(4,1,0)
           200 (2,0,1),(3,0,1)

    SQL>
     
     
  6. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thank you very much sir for this valuable information. Hats off to you too Sir:)

    Regards
    Laxman