April 9, 2011 at 4:34 pm
Can someone help with the following:
I need to combine column 3 into a single string for each of the urn columns, and insert the results into table #tmp2
so for example each row in the result table #tmp2 should read.
urn str
1 this is the first sentence
2 this is the second sentence
see script below:
BEGIN TRAN
declare @string varchar(100)
set @string = ''
create table #tmp
(id int identity(1,1), urn int, str varchar(100))
create table #tmp2
(id2 int identity(1,1), urn2 int, str2 varchar(500))
insert into #tmp
(urn, str)
select 123,'this '
insert into #tmp
(urn, str)
select 123,'is the first '
insert into #tmp
(urn, str)
select 123,'sentence '
insert into #tmp
(urn, str)
select 276,'and this '
insert into #tmp
(urn, str)
select 276,'is the '
insert into #tmp
(urn, str)
select 276,'second sentence '
select @string = @string + str
FROM #tmp
select * from #tmp
select @string
select * from #tmp2
ROLLBACK
April 10, 2011 at 7:51 pm
Add this at the end of your insert statements:
declare @result varchar(500)
declare @urn int
declare Str_cur cursor
for select distinct urn from #tmp
open str_cur
fetch next from str_cur into @urn
While @@FETCH_STATUS >= 0
begin
set @result = ''
select @result=coalesce(@result+' '+rtrim(str),rtrim(str))
from #tmp where urn = @urn
insert #tmp2 select @urn,@result
fetch next from str_cur into @urn
end
close str_cur
deallocate str_cur
select * from #tmp2
You will also probably need some sort of ordering column to make sure you get the result in the correct order.
Note the bit that builds the string is only really one line, the cursor is because you have multiple urn values.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 11, 2011 at 1:24 am
Gosh, folks... we don't need a Cursor for this and we certainly don't need a While loop.
Using the given data, the following works a treat and can easily be changed to an INSERT if you need to.
SELECT t1.urn,
CAST(
(
SELECT '' + [str]
FROM #tmp t2
WHERE t2.urn = t1.urn
ORDER BY t2.id
FOR XML PATH(''),TYPE
)
AS VARCHAR(MAX))
FROM #tmp t1
GROUP BY t1.urn
ORDER BY t1.urn
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2011 at 7:14 am
Jeff
Thanks for the help,
I had already used a cursor but was looking for a more elegant solution.
What I didnt tell you, (for ease of example) is that each line contains html code, so although the XML answer works brilliantly, it replaces the html tags with different chacters. See script below, is there a way to stop this from happening?
BEGIN TRAN
declare @string varchar(100)
set @string = ''
create table #tmp
(id int identity(1,1), urn int, str varchar(100))
create table #tmp2
(id2 int identity(1,1), urn2 int, str2 varchar(500))
insert into #tmp
(urn, str)
select 123,'<table width="100%">'
insert into #tmp
(urn, str)
select 123,'<tr><td width="50">'
insert into #tmp
(urn, str)
select 123,' </td>'
insert into #tmp
(urn, str)
select 276,'<table width="90%">'
insert into #tmp
(urn, str)
select 276,'<tr><td width="40">'
insert into #tmp
(urn, str)
select 276,' </td>'
--select @string = @string + str FROM #tmp
insert into #tmp2
(urn2, str2)
SELECT t1.urn,
CAST(
(
SELECT '' + [str]
FROM #tmp t2
WHERE t2.urn = t1.urn
ORDER BY t2.id
FOR XML PATH(''),TYPE
)
AS VARCHAR(MAX))
FROM #tmp t1
GROUP BY t1.urn
ORDER BY t1.urn
select * from #tmp2
ROLLBACK
April 14, 2011 at 2:04 pm
change it as shown in bold to:
insert into #tmp2
(urn2, str2)
SELECT t1.urn,
CAST(
(
SELECT '' + [str]
FROM #tmp t2
WHERE t2.urn = t1.urn
ORDER BY t2.id
FOR XML PATH(''),TYPE
).value('.','varchar(max)')
AS VARCHAR(MAX))
FROM #tmp t1
GROUP BY t1.urn
ORDER BY t1.urn
select * from #tmp2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply