Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: query on one line

    Hi,
    I've these tables:

    tab1:
    WR_ID
    1
    2
    3
    4
    5
    6

    tab2:
    WR_ID......APPROV
    1............A1
    1............A2
    2............A22
    2............A34
    2............A44
    3............AAA

    I'd like this output:
    WR_ID......APPROV
    1............A1, A2
    2............A22, A34, A44
    3............AAA


    How can I write my query to get this output?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    try this.

    select wr_id,
    max(decode(my_seq,1,approv)) || ' ' ||
    max(decode(my_seq,2,approv)) || ' ' ||
    max(decode(my_seq,3,approv)) approv_list
    from (
    select wr_id,
    approv,
    row_number() over
    (partition by wr_id order by approv) my_seq
    from tab2
    )
    group by wr_id
    order by 1
    /

    WR_ID APPROV_LIST
    --------- --------------------------------
    1 A1 A2
    2 A22 A34 A44
    3 AAA

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by neema
    try this.

    select wr_id,
    max(decode(my_seq,1,approv)) || ' ' ||
    max(decode(my_seq,2,approv)) || ' ' ||
    max(decode(my_seq,3,approv)) approv_list
    from (
    select wr_id,
    approv,
    row_number() over
    (partition by wr_id order by approv) my_seq
    from tab2
    )
    group by wr_id
    order by 1
    /

    WR_ID APPROV_LIST
    --------- --------------------------------
    1 A1 A2
    2 A22 A34 A44
    3 AAA

    neema your query is correct in this case, but if I have one wr_id with 200 various approv I must write 200 statement: max(decode(my_seq,1,approv)) ??

    Is correct?
    Thanks

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I'm not sure how well this scales and it ain't pretty, but I'm quite proud of it all the same (requires 9i):
    Code:
    SELECT wr_id
         , LTRIM(MAX(SYS_CONNECT_BY_PATH(approv,','))
           KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
    FROM   ( SELECT wr_id
                  , approv
                  , ROW_NUMBER() OVER (PARTITION BY wr_id ORDER BY approv) AS curr
                  , ROW_NUMBER() OVER (PARTITION BY wr_id ORDER BY approv) -1 AS prev
             FROM   tab2 )
    GROUP BY wr_id
    CONNECT BY prev = PRIOR curr AND wr_id = PRIOR wr_id
    START WITH curr = 1;
    There are other solutions.

    btw, I don't think I've never needed to do this. What on Earth is it for?

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Use a user_Defined Function

    Since you do not know the number of values in the second column, the simplest way is to use a user-defined function.

    Code:
     
    CREATE OR REPLACE FUNCTION collect_values(p_wr_id in tab1.wr_id%type)
    Return varchar2 is 
    all_values varchar2(4000);
    begin
    for rec in (select approv ||', ' as value from tab2
    				where wr_id = p_wr_id)
    loop
    	 all_values := all_values || rec.value;
    end loop;
    return rtrim(all_values, ', ');
    end collect_values;
    Then use it in sql like this:
    Code:
     
    select wr_id, collect_values(wr_id) as alll_apprvals from tab1;

    You may later want to generalize the function collect_values so that it works with any table and any column.

    Hope that helps.
    Ravi

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Quote Originally Posted by WilliamR
    btw, I don't think I've never needed to do this. What on Earth is it for?
    I've often wondered too Bill - I see these posts every week here and on OTN for similar queries. My guess is that management has requested a report with "user friendly" output. Instead of using a proper report writing tool, the developers are forced to fudge a query.

    For what its worth, it would be SOOO much easier Raf if you just did this in SQL*PLUS and set break on WR_ID. Then you would effectively get a group left report. I can't see any reason they need to be on the same line.

    Normally I'm not such a jerk about it, but if someone can come up with a better reason to output it on one line and go through writing a function to do it, please tell me!!

    Just do this:
    Code:
    Break on WR_ID
    select wr_id, approv
    from tab2
    order by 1,2
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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