How to combine rows in SQL Server 2000
Asked
Active
Viewed 182 times
2 Answers
1
Have you tried using FOR XML RAW in SQL Server 2000?
-
@learner: Not sure why you edited your original post to remove all of the details, but I'd be curious to see how you used XML RAW in a single query to achieve the output in the format you specified in your original question. Please post your solution. – Joe Stefanelli Jul 09 '10 at 13:51
-
@learner - could you reinstate your code or provide another example or post another question to see if we could help again? – Cade Roux Jul 13 '10 at 13:57
0
You can create a user defined function to perform the string concatenation for each ID value.
create table t (id int,start varchar(100),finish varchar(100))
insert into t
select 1,'Start_Main', '' union all
select 1,'Start_Submain1', '' union all
select 2,'Start_Main', '' union all
select 2,'Start_Submain2', 'End_Submain2' union all
select 2,'Start_Submain3', 'End_Submain3' union all
select 2,'Start_Submain1', '' union all
select 2,'Start_Submain4', 'End_Submain4'
Select * from t
go
/* User Defined Function to perform string concatenation per ID */
create function udfStringConcat (@ID int)
returns varchar(500)
as
begin
declare @x varchar(500)
set @x = ''
select @x = @x + t.start + ',' + case when t.finish <> '' then t.finish + ',' else t.finish end
from t
where t.id = @ID
select @x = @x + 'End_Submain1,End_Main'
return @x
end
go
select id, dbo.udfStringConcat(id)
from t
group by id
go
drop function udfStringConcat
drop table t
go
Joe Stefanelli
- 132,803
- 19
- 237
- 235
-
That is what I don't want to do. I want to do it in a single query(like for xml path() in sql 2005). – learner Jul 06 '10 at 03:45