Re: Return rows as 1 line?
From: ddf <oratune_at_msn.com>
Date: Mon, 17 May 2010 20:28:04 -0700 (PDT)
Message-ID: <1a6d59b2-d80f-42e9-9290-a1cc68fc33b1_at_s13g2000prc.googlegroups.com>
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 -
22 end;
23 /
1:ABC:123,1:DEF:456 SQL> select concat_sku_data(2) from dual;
2:MNO:999,2:XYZ:000 SQL> select concat_sku_data(3) from dual;
3:GHI:888 SQL> David Fitzjarrell Received on Mon May 17 2010 - 22:28:04 CDT
Date: Mon, 17 May 2010 20:28:04 -0700 (PDT)
Message-ID: <1a6d59b2-d80f-42e9-9290-a1cc68fc33b1_at_s13g2000prc.googlegroups.com>
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 Received on Mon May 17 2010 - 22:28:04 CDT