Re: Return rows as 1 line?

From: The Magnet <art_at_unsu.com>
Date: Tue, 18 May 2010 09:25:46 -0700 (PDT)
Message-ID: <74ac8845-c095-4f88-97ec-d599073dbce5_at_40g2000vbr.googlegroups.com>



On May 17, 10:28 pm, ddf <orat..._at_msn.com> wrote:
> On May 17, 9:36 pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > On May 17, 4:02 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > On May 17, 1:17 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > > > Hi,
>
> > > > Ok, I am hoping this is now going to be nuts, but say I have data like
> > > > this:
>
> > > > ID    PRODUCT     SKU
> > > > 1         ABC           123
> > > > 1         DEF           456
> > > > 2         XYZ            000
> > > > 2         MNO          999
> > > > 3         GHI            888
>
> > > > And this is what I would need:
>
> > > > 1,ABC:123,DEF:456
> > > > 2,XYZ:000,MNO:999
> > > > 3,GHI:888
>
> > > > So, records that have a certain criteria would have certain columns
> > > > concatenated.  I'm thinking of putting together a dynamic query and
> > > > then opening a cursor and selecting from it, creating the string as I
> > > > go along.
>
> > > > Any thoughts?  I'm going to go with the dynamic cursor unless someone
> > > > else has a better idea.
>
> > >http://asktom.oracle.com/pls/asktom/f?p=100:11:4579694231612304::::P1...
>
> > > jg
> > > --
> > > _at_home.com is bogus.
> > > B of A branch in a van down by the river (click on aerial or birds-eye
> > > view):http://locators.bankofamerica.com/locator/locator/3881__Alton__Parkwa...
>
> > The problem is much more complex than that.  Here is the select
> > statement:
>
> > SELECT article_id, teaser_subject, publish_date, status_id, user_id,
> > category_id, username, status_text, category_type, "TAG DATA"
>
> > Where you see the "TAG DATA" element, I'll need to get values in this
> > format:  "1:A,2:B,3:C"...... from the table.
>
> > So, I do not think GROUP BY will work in this situation.
>
> > BTW:  We are on 10G.   I've looked at some of the CONNECT BY stuff,
> > but not sure that will work.- Hide quoted text -
>
> > - Show quoted text -
>
> You could write  a function to return the concatenated results:
>
> SQL> create table prod_sku(id number,
>   2                        product varchar2(20),
>   3                        sku  varchar2(8));
>
> Table created.
>
> SQL>
> SQL> insert all
>   2  into prod_sku
>   3  values (1,'ABC','123')
>   4  into prod_sku
>   5  values (1,'DEF','456')
>   6  into prod_sku
>   7  values (2,'XYZ','000')
>   8  into prod_sku
>   9  values (2,'MNO','999')
>  10  into prod_sku
>  11  values (3,'GHI','888')
>  12  select * from dual;
>
> 5 rows created.
>
> SQL>
> SQL> create or replace function concat_sku_data(p_id in number)
>   2  return varchar2
>   3  is
>   4  cursor get_concat_vals is
>   5         select id||':'||product||':'||sku convals
>   6         from prod_sku
>   7         where id = p_id
>   8         order by product;
>   9
>  10         v_ctr number:=0;
>  11         data_string varchar2(200);
>  12  begin
>  13         for trec in get_concat_vals loop
>  14          if v_ctr = 0 then
>  15             data_string := trec.convals;
>  16             v_ctr := 1;
>  17          else
>  18             data_string := data_string||','||trec.convals;
>  19          end if;
>  20         end loop;
>  21         return(data_string);
>  22  end;
>  23  /
>
> Function created.
>
> SQL>
> SQL> show errors
> No errors.
> SQL>
> SQL> column concat_sku_data format a40
> SQL> select concat_sku_data(1) from dual;
>
> CONCAT_SKU_DATA(1)
> --------------------------------------------------------------------------------
> 1:ABC:123,1:DEF:456
>
> SQL> select concat_sku_data(2) from dual;
>
> CONCAT_SKU_DATA(2)
> --------------------------------------------------------------------------------
> 2:MNO:999,2:XYZ:000
>
> SQL> select concat_sku_data(3) from dual;
>
> CONCAT_SKU_DATA(3)
> --------------------------------------------------------------------------------
> 3:GHI:888
>
> SQL>
>
> David Fitzjarrell

David, I like your solution. But I wanted to make sure we are on the same page:

The package accepts like 7 'optional' criteria parameters. The query criteria is then dynamically put together based on the parameters passed.

2 columns in the result set, TAG & TAG_ID, need to be returned not as separate columns, but in the format of TAG:TAG_ID, TAG:TAG_ID, etc.

So, here is an example: Say the procedure looks like this:

PROCEDURE x (p_category NUMBER, p_user_id NUMBER, p_sort VARCHAR2, p_data OUT REF_CRS);

Now, the criteria is put together for the values which actually have values passed. When the query is executed it will return a record set with the TAG & TAG_ID columns. But I would need to group all of the like rows together and with those 2 columns created in the concatenated value I need:

CATEGORY     USER   TAG    TAG_ID
A                      12        XX        24
B                      43        XX        24
A                      12        YY       17
A                      12        ZZ        11

Result:
A,12,24:XX,17:YY,11:ZZ
B,43,24:XX

Does that make sense? I'm thinking this is going to be more complex, with maybe a few collections and such, and trying to maintain the sort order. Received on Tue May 18 2010 - 11:25:46 CDT

Original text of this message