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: The Magnet on 17 May 2010 16:17 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.
From: joel garry on 17 May 2010 17:02 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::::P11_QUESTION_ID:229614022562 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__Parkway,__Suite__B_92606_IRVINE_CA/bank_branch_locations/
From: The Magnet on 17 May 2010 21:36 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.
From: Mladen Gogala on 17 May 2010 21:36 On Mon, 17 May 2010 14:02:24 -0700, joel garry 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::::P11_QUESTION_ID:229614022562 > > jg 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. -- http://mgogala.byethost5.com
From: ddf on 17 May 2010 23:28
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 |