如何將檢索後多列的數據用一行字段進行顯示？

[TyroneChen]

比如有以下一組數據：
=======================================================
cgdh sl prdj
----------- ------------ ------------
PO11031639 1.0000 W/03-127
PO11031639 2.0000 W/03-128
PO11031639 7.0000 W/03-129
PO11031639 4.0000 W/03-130
========================================================
我想以這樣的形式檢索出來：
========================================================
cgdh sl prdj
----------- ------------ ------------
PO11031639 14.000 W/03-127 W/03-128 W/03-129 W/03-130
========================================================


謝謝！！！

[txlicenhe]

http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.6941645
[交流]行列转换

[pengdali]

create function getstr(@content varchar(20))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+' '+rtrim(prdj) from 表 where cgdh=@content
set @str=ltrim(@str)
return @str
end
go

--语句：
select cgdh,sum(sl) sl,dbo.getstr(cgdh) prdj from 表 group by cgdh

[txlicenhe]

--1.创建一个合并的函数
create function fmerg(@id char(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+prdjfrom 表A where cgdh=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select cgdh,sum(sl) as sl,dbo.fmerg(cgdh) from 表A
group by cgdh

[zjcxc]

--7.0用临时表
select *,aa=cast('' as varchar(8000)) into #t from 表 order by cgdh
declare @cgdh varchar(10),@prdj varchar(8000)
update #t set @prdj=case @cgdh when cgdh then @prdj+' '+prdj else prdj end
,aa=@prdj,@cgdh=cgdh

select cgdh,sl=sum(sl),prdj=max(aa) from #t group by cgdh

drop table #t

[joycezhou]

速度最快应是建temp table

[TyroneChen]

受益非淺，基本上問題已經解決了

[TyroneChen]

買單，謝謝各位大哥