Return rows as 1 line?
in [Oracle]
|
Prev: dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE cannot trace service in job class
Next: Why export is not a good archiving tool
From: bugbear on 18 May 2010 04:38 Mladen Gogala wrote: > To tell the truth, I don't really like Tom's answer, either. Oracle is a > relational database. Relational databases should return the requested > rows, as quickly as possible. Relational databases are not formatting > tools. Formatting the output is a job for the client tools, not for > Oracle. Agreed. BugBear
From: The Magnet on 18 May 2010 12:25 On May 17, 10:28 pm, ddf <orat...(a)msn.com> wrote: > On May 17, 9:36 pm, The Magnet <a...(a)unsu.com> wrote: > > > > > On May 17, 4:02 pm, joel garry <joel-ga...(a)home.com> wrote: > > > > On May 17, 1:17 pm, The Magnet <a...(a)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 > > > -- > > > @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.
From: The Magnet on 18 May 2010 13:16 On May 18, 11:25 am, The Magnet <a...(a)unsu.com> wrote: > On May 17, 10:28 pm, ddf <orat...(a)msn.com> wrote: > > > > > On May 17, 9:36 pm, The Magnet <a...(a)unsu.com> wrote: > > > > On May 17, 4:02 pm, joel garry <joel-ga...(a)home.com> wrote: > > > > > On May 17, 1:17 pm, The Magnet <a...(a)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 > > > > -- > > > > @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.
From: joel garry on 18 May 2010 13:46 On May 18, 1:38 am, bugbear <bugbear(a)trim_papermule.co.uk_trim> wrote: > Mladen Gogala wrote: > > To tell the truth, I don't really like Tom's answer, either. Oracle is a > > relational database. Relational databases should return the requested > > rows, as quickly as possible. Relational databases are not formatting > > tools. Formatting the output is a job for the client tools, not for > > Oracle. > > Agreed. > > BugBear I don't agree that this is mere formatting, it is using the relational access tool and its non-relational extensions to transform tuples into a non-relational structure. I agree this would be some bad mojo if done thousands of time a minute. jg -- @home.com is bogus. I feel so stupid and ignorant. http://www.signonsandiego.com/news/2010/may/18/developer-5-others-indicted-in-condo-deal/
From: The Magnet on 18 May 2010 13:56 On May 18, 12:46 pm, joel garry <joel-ga...(a)home.com> wrote: > On May 18, 1:38 am, bugbear <bugbear(a)trim_papermule.co.uk_trim> wrote: > > > Mladen Gogala wrote: > > > To tell the truth, I don't really like Tom's answer, either. Oracle is a > > > relational database. Relational databases should return the requested > > > rows, as quickly as possible. Relational databases are not formatting > > > tools. Formatting the output is a job for the client tools, not for > > > Oracle. > > > Agreed. > > > BugBear > > I don't agree that this is mere formatting, it is using the relational > access tool and its non-relational extensions to transform tuples into > a non-relational structure. > > I agree this would be some bad mojo if done thousands of time a > minute. > > jg > -- > @home.com is bogus. > I feel so stupid and ignorant.http://www.signonsandiego.com/news/2010/may/18/developer-5-others-ind... Well, looks like PL/SQL and maybe some collections. Sigh.
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE cannot trace service in job class Next: Why export is not a good archiving tool |