SQLTeam.com | Weblogs | Forums

Stuff function issues


#1

Hei guys, i found this site very helpful and maybe you can help me this time.
I tried to use Stuff function but it doesnt retreive what i expect and i dont know what im doing wrong. i have this table and i need to fill in the column "Concatenate"

As you can see for every distinct Final Hunter Match, i have different Ops ID's. I need to concatenate these Ops Id's into the last column. this is what i have so far:

select [Final Hunter Match],[Ops Id],[Number], stuff ((select ','+ [Concatenate]
from table t2
where t2.[Final Hunter Match]=t1.[Final Hunter Match]
for XML path ('')),1,1,'')as [Col1]
from [dbo].table t1

thank you!


#2

sorry, I don't understand your request.
Base on your query, just small changes ...

DECLARE  @table TABLE
(
	[Final Hunter Match] varchar(50),
	[Ops Id] varchar(30),
	[Number] tinyint
)
insert into @table([Final Hunter Match],[Ops Id],[Number])
VALUES('OPE1','OPE1-58',3),('OPE1','OPE1-50',3),('OPE1','OPE1-118',3)


select [Final Hunter Match],[Ops Id],[Number], 
		stuff (
				(select ','+ [Ops Id]
				from @table  AS t2
				where t2.[Final Hunter Match]=t1.[Final Hunter Match]
				for XML path ('')
				),1,1,'')as [Col1]
from @table AS t1


Final Hunter Match Ops Id                         Number Concatenate
OPE1               OPE1-58                        3      OPE1-58,OPE1-50,OPE1-118
OPE1               OPE1-50                        3      OPE1-58,OPE1-50,OPE1-118
OPE1               OPE1-118                       3      OPE1-58,OPE1-50,OPE1-118

with apply:

select
	[Final Hunter Match],[Ops Id],[Number]
	, stuff(CA.concatenate,1,1,'') as [Concatenate]
from @table as t1
cross apply
		(select  ','+ [Ops Id]
		 from @table as t2
		 where t2.[Final Hunter Match]=t1.[Final Hunter Match]
		for XML path ('')
		)CA(Concatenate)

#3

thank you very much it works when i am making the simple select but when i try to update the table it gives me an error: Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated.

i wrote like this:
insert into table
([Final Hunter Match], [Ops Id],[Number],[Col1])
select(Final Hunter Match], [Ops Id],[Number],
stuff (
(select ','+ [Ops Id]
from @table_a AS t2
where t2.[Final Hunter Match]=t1.[Final Hunter Match]
for XML path ('')
),1,1,'')as [Col1]
from table_a as t1


#4

Place a Left for the column , with the size of your table field


#5

sorry but i dont understand what you mean :smiley:
I tried to modify the size of the column from nvarcar(255) to nvarchar(900) and
it still gives me this error or i got you wrong?


#6

i fixed it, put nvarchar(4000) and it seems that it works. thank you so much for your help.


#7

I mean , to "trim" the return number of characters in your select.
Just to be safe, if you are inserting into this table :

insert into table
([Final Hunter Match], [Ops Id],[Number]) 
select  ....

this is running ok ? In this way , you will know that truncation is happening on col1

insert into table
([Final Hunter Match], [Ops Id],[Number],[Col1])
select(Final Hunter Match], [Ops Id],[Number],
LEFT(stuff (
(select ‘,’+ [Ops Id]
from @table_a AS t2
where t2.[Final Hunter Match]=t1.[Final Hunter Match]
for XML path (’’)
),1,1,’’),255)
from table_a as t1