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!
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)
1 Like
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
Place a Left for the column , with the size of your table field
sorry but i dont understand what you mean
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?
i fixed it, put nvarchar(4000) and it seems that it works. thank you so much for your help.
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