February 27, 2009 at 4:01 pm
I have a table with the following values:
col1col2
emp1aa
emp1bb
emp1cc
emp1dd
emp2qq
emp2ww
emp2ee
.... ...
... ...
I am just giving sample table data, the table will have more rows than that.
I am trying to create third column by concatenating values of col2 with a comma as follows:
col1col2col3
emp1aaaa,bb,cc,dd
emp1bbaa,bb,cc,dd
emp1ccaa,bb,cc,dd
emp1ddaa,bb,cc,dd
emp2qqqq,ww,ee
emp2wwqq,ww,ee
emp2eeqq,ww,ee
please advice how I can have col3 displayed in the final output data. Thx.
February 27, 2009 at 4:06 pm
here's an example:
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
Lowell
February 28, 2009 at 6:07 am
Mh (2/27/2009)
I am trying to create third column by concatenating values of col2 with a comma as follows:
Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2009 at 3:47 am
May be if you want to do this frequently, then create an udf and do the update as shown below.
CREATE FUNCTION dbo.ALLVALUES(@Category varchar(100))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @allnames varchar(1000)
SELECT @allnames = COALESCE(@allnames +',','') +CAST(name AS VARCHAR(20))
FROM TEST
Where category = @Category
RETURN @allnames
END
UPDATE TEST
SET allnames = dbo.ALLVALUES(category)
FROM TEST
---------------------------------------------------------------------------------
March 1, 2009 at 6:28 pm
I'm with Gail... WHY do you want to do this to a perfectly good database? What are the business reasons for this?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2009 at 1:10 pm
I second jeff and Gail, there's no need to keep this data in the table all the time.
Build it in the GUI when presenting the data, otherwise leave the design alone.
If that'snot possible for you then WHY?
March 2, 2009 at 1:24 pm
Third vote for Jeff/Gail
March 2, 2009 at 1:29 pm
Lol, ok so you have the top 4 "gurus" on this site which all combined have answered over 10% of all the questions ever posted on this site.
If that doesn't tell you something, I don't know what will.
March 2, 2009 at 1:38 pm
Let's up the numbers. I'm curious as to why you need to do this as well.
March 2, 2009 at 1:45 pm
Add me to the list of Curious.
"Curiosity killed the cat" they say..:)
-Roy
March 2, 2009 at 1:46 pm
Roy Ernest (3/2/2009)
Add me to the list of Curious."Curiosity killed the cat" they say..:)
Satisfaction brought him back...
March 2, 2009 at 2:34 pm
If there is a good, reason how is this? You can use cursor but I prefer using identity field which I added to table. If the extra comma is not desired at the end of the string additional logic can be added.
alter table table1 add col4 int identity (1,1) /*in place of cursor */
declare @min-2 int
declare @max-2 int
declare @counter int
declare @emp varchar(10)
declare @code varchar(10)
Update table1 set col3='' where col3 is null /*cannot add string to null */
select @counter= min(col4) from table1
select @max-2=max(col4) from table1
while @counter<=@max
Begin
Select @emp=col1 from table1 where col4=@counter
select @code=col2 from table1 where col4=@counter
update table1 set col3=col3+@code+ ','
where col1=@emp
Select @counter=@counter+1
End
MPG
March 2, 2009 at 2:38 pm
What has been asked can be done without a cursor or while loop. What we are looking for is why this needs to be done in the database. There are other alternatives, including doing the concatenation in the user interface code.
March 2, 2009 at 2:42 pm
The question "why" is important here not to tell you that you shouldn't find a solution to your core problem so much as to ask you to state the REAL problem you're trying to solve with a TSQL query. Perhaps there's a more appropriate solution to what I can only guess may be a request to provide pivoted data from a database in a CSV format.
March 2, 2009 at 6:19 pm
ganci.mark (3/2/2009)
If there is a good, reason how is this? You can use cursor but I prefer using identity field which I added to table. If the extra comma is not desired at the end of the string additional logic can be added.alter table table1 add col4 int identity (1,1) /*in place of cursor */
declare @min-2 int
declare @max-2 int
declare @counter int
declare @emp varchar(10)
declare @code varchar(10)
Update table1 set col3='' where col3 is null /*cannot add string to null */
select @counter= min(col4) from table1
select @max-2=max(col4) from table1
while @counter<=@max
Begin
Select @emp=col1 from table1 where col4=@counter
select @code=col2 from table1 where col4=@counter
update table1 set col3=col3+@code+ ','
where col1=@emp
Select @counter=@counter+1
End
MPG
There's actually a number of very high speed methods that you can use for this instead of resorting to a WHILE loop. Please see the following article for those and some caveats to avoid...
http://www.sqlservercentral.com/articles/Test+Data/61572/
I'd still like to know what the business reasons behind this are because this isn't usually a good idea to do in a database. If we were to know those BR's, perhaps we could find a better solution for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply