Updating many rows into single cell
-
Hi All,
This task sounded easy when I was first given it - I appear to be wrong!
I have a table structure like this...
id category name allnames
------ ---------------- -------------- ------------------------
1 fruit apple
2 fruit pear
3 fruit orange
4 meat beef
5 meat pork
6 meat gammon
What I need is a way to update the allnames column so that it is like this..
id category name allnames
------ ---------------- -------------- ------------------------
1 fruit apple apple, pear, orange
2 fruit pear apple, pear, orange
3 fruit orange apple, pear, orange
4 meat beef beef, pork, gammon
5 meat pork beef, pork, gammon
6 meat gammon beef, pork, gammon
I have found some code which will select out the values from the first table in a csv string into a variable, but can't find a way to update them back into the table.
I could use a cursor of course, but there are many rows in this table, and a cursor takes far too long and is, as we all know, far too inefficient.
Any ideas?
Many thanks,
Martin
-
using your fruittable as a model, maybe something like this will help?
you'd still need to use a cursor, but just one cursor for each category; the code for within the cursor is bold
create table #fruittable(
id int identity(1,1) primary key,
category varchar(30),
name varchar(30),
allnames varchar(200) )
insert into #fruittable(category,name) values('fruit','apple')
insert into #fruittable(category,name) values('fruit','pear')
insert into #fruittable(category,name) values('fruit','orange')
insert into #fruittable(category,name) values('meat','beef')
insert into #fruittable(category,name) values('meat','pork')
insert into #fruittable(category,name) values('meat','gammon')
declare @fruitlist varchar(200)
set @fruitlist=''
select @fruitlist=@fruitlist + isnull(name,'') + ',' from #fruittable where category='fruit'
print @fruitlist
update #fruittable set allnames=@fruitlist where category='fruit'
select * from #fruittable
set @fruitlist=''
select @fruitlist=@fruitlist + isnull(name,'') + ',' from #fruittable where category='meat'
print @fruitlist
update #fruittable set allnames=@fruitlist where category='meat'
select * from #fruittable
you'd need to strip the last comma, but this is pretty close:
results:
1 fruit apple apple,pear,orange, 2 fruit pear apple,pear,orange, 3 fruit orange apple,pear,orange, 4 meat beef beef,pork,gammon, 5 meat pork beef,pork,gammon, 6 meat gammon beef,pork,gammon, Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
You dont need to use a cursor here...
This solution does rely on the data being ordered by category in the test table but you can always preorder it. This should be much faster than a cursor.
------------DDL----------------
create table test (id int identity, category varchar(100),
name varchar(100), allnames varchar(8000) null)
insert test (category, name)
select 'fruit', 'apple' union
select 'fruit', 'pear' union
select 'fruit', 'orange' union
select 'meat' , 'beef' union
select 'meat' , 'pork' union
select 'meat' , 'gammon'
------------------------------------
declare @category varchar(100), @name varchar(100)
select @name ='' ,@category =category from test where id = 1
update t
set @name = case when @category = category then @name +','+name else name end , allnames = @name, @category = category
from test t
update test
set allnames = x.allnames
from test t
join (select max(allnames)allnames, category from test group by category)x
on x.category = t.category
----------------------------------------------
-
--As A cursor solution:
declare
@fruitlist varchar(2000),
@categ varchar(64)
declare c1 cursor for select distinct category from #fruittable
open c1
fetch next from c1 into @categ
While @@fetch_status <> -1
begin
set @fruitlist=''
select @fruitlist=@fruitlist + isnull(name,'') + ',' from #fruittable where category=@categ
if len(@fruitlist) > 1
--trim trailing
set @fruitlist = substring(@fruitlist,1,len(@fruitlist) -1)
update #fruittable set allnames=@fruitlist where category=@categ
fetch next from c1 into @categ
end
close c1
deallocate c1
select * from #fruittable
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks very much for your replies everybody. One thing my simplistic example didn't make very obvious is that there could be up to 40,000 rows in the table, so using a cursor would be a last resort. Jules, your solution is now in production and doing a damn fine job!!
Thanks again
-
Very clever.
Slight mod for you...Take out the initial variable load and you won't get the leading (or trailing) commas... change the two variable lengths also gets rid of the long record error.
------------DDL----------------
create table test (id int identity, category varchar(100),
name varchar(100), allnames varchar(8000) null)
insert test (category, name)
select 'fruit', 'apple' union
select 'fruit', 'pear' union
select 'fruit', 'orange' union
select 'meat' , 'beef' union
select 'meat' , 'pork' union
select 'meat' , 'gammon'
------------------------------------
declare @category varchar(20), @name varchar(20)
--select @name ='' ,@category =category from test where id = 1
update t
set @name = case when @category = category then @name +','+name else name end , allnames = @name, @category = category
from test t
update test
set allnames = x.allnames
from test t
join (select max(allnames)allnames, category from test group by category)x
on x.category = t.category
I'd also suggest that you might want to do this in a separate table so you don't have quite as much denormalization... of course, denormalized data is nasty fast.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Even better - Thanks, Jeff.
This is definately one for my code snippets library
-
I certainly wouldn’t recommend storing the data in this format, as you correctly point out it isn’t even in 1st normal form.(3rd is the minimum i would recommend). However i assume this formatting is done on the fly within a stored procedure and stored in temp table.
The only reason for doing this is for performance and, as i am sure you know, the relational model does sometimes have to be adapted to cope with the practical limitations of current technology. Running a procedural loop through 40,000 rows on puny client machines for example may not acceptable.
Having said that I will always encourage you brown shirts of the relational model to enforce conformance to the only solid theoretical foundation available to us.
In this instance though my idealistic friend, you are preaching to the converted.
-
I don't think he intends to store that in his database, Joe. 3rd party vendors frequently as for data in such a form. And, no, there's not always a front end application available to do it for you especially if it's an ad-hoc request.
Or are you just a new, bad SQL programmer looking for some proprietary kludge to avoid the effort to learn to do it right? There's a lot of good ways to say that what someone is doing might be wrong... I'm thinking the method above isn't one of them...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi again,
apologies for being neglecting my post - I have been away for the weekend.
you are correct, Jeff and Jules. This data is held in a 'cache' table. The user uses a thin client (intranet) to select criteria to display summary financial information back to his web browser. The browser displays url's for each summary figure so that the user can 'drill down' into the data (user clicks on url to drill into, another call is made to the .net web application which will retrieve data from the database). This data drill down has to be very fast, which is the reason for storing this data temporarily in this manner. The data is deleted when the users session ends.
Joe, I guess this is a real world example of Lowell's brilliant signature above - "In Theory, theory and practice are the same...In practice, they are not" - I have been a sql programmer for several years, and am well aware of the basic fundamentals behind normalization, but sometimes there are valid reasons for not following them. Make sure you have the facts before being openly critical!
Martin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply