在 SQL Server 中合并字段

Question

如何在 SQL Server 2000 中合并行

Answer 1

您是否尝试过在 SQL Server 中使用 FOR XML RAW 2000？

Answer 2

您可以创建一个用户定义的函数来对每个 ID 值执行字符串串联。

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