Re: Return rows as 1 line?
From: The Magnet <art_at_unsu.com>
Date: Tue, 18 May 2010 10:16:50 -0700 (PDT)
Message-ID: <763152ac-7b71-49ea-ab77-2853cce28a30_at_f14g2000vbn.googlegroups.com>
On May 18, 11:25 am, The Magnet <a..._at_unsu.com> wrote:
> 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.
Date: Tue, 18 May 2010 10:16:50 -0700 (PDT)
Message-ID: <763152ac-7b71-49ea-ab77-2853cce28a30_at_f14g2000vbn.googlegroups.com>
On May 18, 11:25 am, The Magnet <a..._at_unsu.com> wrote:
> 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.
I know I can probably crate some functions and such, but I was hoping to use something like CONNECT BY or something so I do not have code write yet more code. Received on Tue May 18 2010 - 12:16:50 CDT