IDNO compName other
111 aa 221
111 bb 333
222 cc 444
222 ff 444
333 gg 555
444 ff 666
我想要通过select后得到:
111 aa,bb
222 cc,ff
33 gg
44 ff
该如何写sql呢
111 aa 221
111 bb 333
222 cc 444
222 ff 444
333 gg 555
444 ff 666
我想要通过select后得到:
111 aa,bb
222 cc,ff
33 gg
44 ff
该如何写sql呢
returns varchar(100)
as
begin
declare @s varchar(1000)
select @s = isnull(@s+',')+compname from ta where idno = @idno
return @s
end
go
select idno ,dbo.f_str(idno)
from ta
group by idno
;
111 aa 221
111 bb 333
222 cc 444
实际贴:http://topic.csdn.net/u/20100720/10/c0b95705-308b-4ef4-b086-77b0c4c04522.htmlhttp://topic.csdn.net/u/20100802/14/8be14ec7-e560-4541-9d06-0a1a9bec17b7.html --建立测试环境
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
id int identity,
houseName varchar(10),
proid int,
procount int
)
GO
INSERT TB
SELECT 'A仓库',1,100 union all
SELECT 'A仓库',2,50 union all
SELECT 'B仓库',1,60 union all
SELECT 'B仓库',2,70
--查询
--1
IF OBJECT_ID('f_getproid') IS NOT NULL DROP function f_getproid
GO
create function f_getproid (@houseName varchar(10))
returns varchar(50)
as
begin
declare @s varchar(50)
select @s=isnull(@s+',','')+ltrim(proid) from tb where houseName=@houseName
return @s
end
go
IF OBJECT_ID('f_getprocount') IS NOT NULL DROP function f_getprocount
GO
create function f_getprocount (@houseName varchar(10))
returns varchar(50)
as
begin
declare @s varchar(50)
select @s=isnull(@s+',','')+ltrim(procount) from tb where houseName=@houseName
return @s
end
go
select houseName,dbo.f_getproid(houseName)proid,dbo.f_getprocount(houseName)procount from tb group by houseName
go
--2
select houseName,
stuff((select ','+ltrim(proid) from tb where houseName=t.houseName for xml path('')),1,1,'') proid,
stuff((select ','+ltrim(procount) from tb where houseName=t.houseName for xml path('')),1,1,'') procount
from tb t
group by houseName
--结果
/*(4 行受影响)
houseName proid procount
---------- ----- --------
A仓库 1,2 100,50
B仓库 1,2 60,70(2 行受影响)houseName proid procount
---------- ----- --------
A仓库 1,2 100,50
B仓库 1,2 60,70(2 行受影响)*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/xys_777/archive/2010/07/07/5718036.aspx
--2
select houseName,
stuff((select ','+ltrim(proid) from tb where houseName=t.houseName for xml path('')),1,1,'') proid,
stuff((select ','+ltrim(procount) from tb where houseName=t.houseName for xml path('')),1,1,'') procount
from tb t
group by houseName
筛选的条件都要带入重新带入到子查询里面