Re: Not sure how to write this in sql

  • From: david wendelken <davewendelken@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 12 Jan 2005 14:24:43 -0800 (PST)

Always think in terms of sets of data!

This code makes several assumptions, which may not be safe ones.
But you can adjust this to handle things appropriately.

1) Never More than 3 rows.  
  (change the decode logic into a loop in a function
   and pass it the counter, return the string 
   if not a safe assumption.
   And it is almost certainly NOT a safe one!
  )
2) Not already in table_b.
  (use the merge command instead of the insert command)
3) Don't care if table_a no longer has the record.
  (You'll need to consider deletes of ids in table_b that are no longer in 
table_a if you are merging).


But this should give you the idea:

insert into table_b b
(id, col2)
select id, decode(counter,1,'1',2,'1,2',3,'1,2,3',NULL)
from
  (select
   a.id, count(a.id) counter
   from table_a a
   group by a.id
  )


--
//www.freelists.org/webpage/oracle-l

Other related posts: