--创建一个合并的函数
create function f_hb(@po_no int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(Pname as varchar) from tb where po_no= @po_no
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct po_no,dbo.f_hb(po_no) as Pname from tb
create function f_hb(@po_no int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(Pname as varchar) from tb where po_no= @po_no
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct po_no,dbo.f_hb(po_no) as Pname from tb
po_no Pname
1 a
1 b
1 b我只想吧a 和单条b放在一起呢
drop table tb
go
create table tb
(
ID int,
VALUE varchar(10)
) insert into tb(ID,VALUE) values(1,'A')
insert into tb(ID,VALUE) values(2,'B')
insert into tb(ID,VALUE) values(3,'C')
insert into tb(ID,VALUE) values(4,'D')
insert into tb(ID,VALUE) values(5,'E' ) declare @sql varchar(8000)
set @sql = 'select '''
select @sql=@sql+VALUE from (select top 2 VALUE from tb order by VALUE)tc
select @sql=@sql+'''' exec(@sql) drop table tb
Create Table 测试表(po_no int,Pname Varchar(5))
Insert 测试表 Select 1, 'a'
Union All Select 1, 'b' ---重复b
Union All Select 1, 'b'
Union All Select 1, 'c'Select * From 测试表---创建函数
Create Function F_MergePname(@po_no int)
Returns Varchar(8000)
As
Begin
Declare @sql varchar(8000)
Set @sql = ''
Select @sql = @sql+','+[Pname] From (
Select Distinct * From 测试表 ) T Where po_no= @po_no
Return Stuff(@sql,1,1,'')
End
Go---调用函数查询结果
Select po_no,dbo.F_MergePname(po_no) As Pname From 测试表 Group By po_no/*
po_no Pname
----------- -----------
1 a,b,c(所影响的行数为 1 行)
*/
po_no
,Sum(Case When Pname=11 then Isnull(Pname,0) else 0 end) as a
,Sum(Case When Pname=22 then Isnull(Pname,0) else 0 end) as b
,Sum(Case When Pname=33 then Isnull(Pname,0) else 0 end) as c
from
aaa
group by po_no