求一SQL語句

[dnvodc]

el_no, bo_no

1號主機 CPU:P166
1號主機 HD:3G
1號主機 MEM:16M
2號主機 CPU:P166
2號主機 HD:3G
2號主機 MEM:16M
4號主機 CPU:K6-300
4號主機 MEM:16M
AAISMT70 CPU:C667
AAISMT70 FLOPPY:1.44
AAISMT70 HD:10G
AAISMT70 MEM:128M
AAISMT70 MON:V510C-15"

要得到的結果是：
1號主機 ,CPU:P166,HD:3G,MEM:16M
2號主機,CPU:P166,HD:3G,MEM:16M
4號主機 CPU:K6-300,MEM:16M
..................................

[Andy__Huang]

參考

create table tb (A varchar(10),B varchar(1000))
Insert into tb
select '111','AA0000，AA0001，AA0002，'
union all select '111','BB0000，BB0001，BB0002，'
union all select '222','AA0120，AA0102，AA1453，'
union all select '222','BB1212，BB3423，BB2123，'

select * from tb

--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end

--刪除
drop table tb
drop function dbo.fn_b

--結果
select A,B=dbo.fn_b(A) from tb group by A
A B
-------------------------------------------
111 AA0000，AA0001，AA0002，BB0000，BB0001，BB0002，
222 AA0120，AA0102，AA1453，BB1212，BB3423，BB2123，

[Andy__Huang]

create table tb(el_no varchar(50), bo_no varchar(100))
Insert into tb
select '1號主機','CPU:P166'
union all select '1號主機','HD:3G'
union all select '1號主機','MEM:16M'
union all select '2號主機','CPU:P166'
union all select '2號主機','HD:3G'
union all select '2號主機','MEM:16M'
union all select '4號主機','CPU:K6-300'
union all select '4號主機','MEM:16M'
union all select 'AAISMT70','CPU:C667'
union all select 'AAISMT70','FLOPPY:1.44'
union all select 'AAISMT70','HD:10G'
union all select 'AAISMT70','MEM:128M'
union all select 'AAISMT70','MON:V510C-15"'

select * from tb

create function dbo.fn_k(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[bo_no]+', ' from tb where el_no=@a
return (left(@s,len(@s)-1))
end

--刪除
drop table tb
drop function dbo.fn_k

--結果
select el_no,bo_no=dbo.fn_k(el_no) from tb group by el_no
el_no bo_no
-------------------------------------
1號主機 CPU:P166, HD:3G, MEM:16M
2號主機 CPU:P166, HD:3G, MEM:16M
4號主機 CPU:K6-300, MEM:16M
AAISMT70 CPU:C667, FLOPPY:1.44, HD:10G, MEM:128M, MON:V510C-15"

[yjdn]

create function f_try(@a varchar(20))
returns varchar(8000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+','+bo_no from 表名 where el_no=@a
return (@s) --你在汇总后，前面还有一个逗号？如果不要的话
改成return(stuff(@s,1,1,''))
end

--运行
select el_no,bo_no=dbo.f_try(el_no) from 表名 group by el_no

--你试一下，我没测试