MS SQL2000中，求SQL语句。急。。。。

[rings2000]

tab1
pid  pname
1    层板
2    顶板
3    中侧板

tab2
id   pid   pcode
1    1     993029
2    1     993030
3    1     993031

4    2     311021
5    2     311022

6    3     223345
7    3     223346
8    3     223347
9    3     223348

MS SQL2000中，如何写一个SQL语句，执行后，查询结果如下：

pid  pname   allcode
1    层板     993029,993030,993031
2    顶板     311021,311022
3    中侧板   223345,223346,223347,223348

[Andy__Huang]

;with tab1(pid , pname) as
(
select 1,'层板'
union all select 2,'顶板'
union all select 3,'中侧板'
),
tab2(id ,  pid ,  pcode) as
(
select 1,1,'993029'
union all select 2,1,'993030'
union all select 3,1,'993031'
union all select 4,2,'311021'
union all select 5,2,'311022'
union all select 6,3,'223345'
union all select 7,3,'223346'
union all select 8,3,'223347'
union all select 9,3,'223348'
),cte as
(
select a.*,b.pname from tab2 a left join tab1 b on a.pid=b.pid
)

select a.pid,a.pname,
stuff((select ','+pcode from cte b 
       where b.pid=a.pid 
       for xml path('')),1,1,'') 'pcode'
from cte a
group by  a.pid,a.pname

/*
pid	pname	pcode
1	层板	993029,993030,993031
2	顶板	311021,311022
3	中侧板	223345,223346,223347,223348
*/

[LongRui888]

2000不支持xml path ，所以用函数来实现，应该没有什么更好的办法了：

create table tab1(pid int,  pname varchar(100));

insert into tab1
select 1,    '层板' union all
select 2,    '顶板' union all
select 3,    '中侧板'

create table tab2(id int,  pid int, pcode varchar(100))

insert into tab2
select 1,    1,     '993029' union all
select 2,    1,     '993030' union all
select 3,    1,     '993031' union all
select 4,    2,     '311021' union all
select 5,    2,     '311022' union all
select 6,    3,     '223345' union all
select 7,    3,     '223346' union all
select 8,    3,     '223347' union all
select 9,    3,     '223348'
go




--drop function dbo.fn_mergeSTr


create function dbo.fn_mergeSTR(@pid int,@split varchar(10))  
returns varchar(300)  
as  
begin  
    declare @str varchar(300);  
      
    set @str = '';  
      
    select @str = @str + pcode + @split  
    from tab2  
    where pid = @pid  
      
    set @str = left(@str , len(@str) - LEN(@split) )  
      
    return @str   --返回值   
end  
go  


select distinct
       t1.pid,
       t1.pname,
       
       dbo.fn_mergeSTR(t1.pid,',') as str
from tab1 t1
/*
pid	pname	pcode
1	层板	993029,993030,993031
2	顶板	311021,311022
3	中侧板	223345,223346,223347,223348
*/

[Andy__Huang]

sql 2000就得写函数了:

create table tab1(pid int, pname varchar(10)) 
insert into tab1
select 1,'层板'
union all select 2,'顶板'
union all select 3,'中侧板'
go

create table tab2(id int,  pid int,  pcode varchar(10)) 
insert into tab2
select 1,1,'993029'
union all select 2,1,'993030'
union all select 3,1,'993031'
union all select 4,2,'311021'
union all select 5,2,'311022'
union all select 6,3,'223345'
union all select 7,3,'223346'
union all select 8,3,'223347'
union all select 9,3,'223348'
go

create function dbo.fn_name( @pid int)
returns varchar(500)
as
begin
declare @s varchar(500)
set @s=''
select @s=@s+','+pcode from tab2 where pid=@pid
if LEN(@s)>0
	set @s=stuff(@s,1,1,'')
return @s
end
go


select b.pid,b.pname ,dbo.fn_name(b.pid) as pname 
from tab2 a left join tab1 b on a.pid=b.pid
group by b.pid,b.pname

drop table tab1,tab2
drop function dbo.fn_name

/*
pid	pname	pname
1	层板	993029,993030,993031
2	顶板	311021,311022
3	中侧板	223345,223346,223347,223348
*/

[發糞塗牆]

没看到你留言了，不好意思，既然那么旧都没结贴，我就写一个,那时下班了，今天忙晕了，忘了还有这贴：
[code=sql]----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-10-18 20:51:27
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[tab1]
if object_id('[tab1]') is not null drop table [tab1]
go 
create table [tab1]([pid] int,[pname] varchar(6))
insert [tab1]
select 1,'层板' union all
select 2,'顶板' union all
select 3,'中侧板'
--> 测试数据：[tab2]
if object_id('[tab2]') is not null drop table [tab2]
go 
create table [tab2]([id] int,[pid] int,[pcode] VARCHAR(10))
insert [tab2]
select 1,1,993029 union all
select 2,1,993030 union all
select 3,1,993031 union all
select 4,2,311021 union all
select 5,2,311022 union all
select 6,3,223345 union all
select 7,3,223346 union all
select 8,3,223347 union all
select 9,3,223348
--------------开始查询--------------------------
--创建函数来显示
go
CREATE FUNCTION f_hb ( @id VARCHAR(10) )
RETURNS VARCHAR(1000)
AS
    BEGIN
        DECLARE @str VARCHAR(1000)
        SET @str = ''
        SELECT  @str = @str + ',' + [pcode]
        FROM    [tab2]
        WHERE   CHARINDEX(',' + CAST(pid AS VARCHAR(256)) + ',',
                          ',' + @id + ',') > 0
        RETURN STUFF(@str,1,1,'')
    END
go 
SELECT  [pid] ,
        [pname] ,
        classid = dbo.f_hb(pid)
FROM    [tab1]
go 
DROP FUNCTION dbo.f_hb
----------------结果----------------------------
/* 
pid         pname  classid
----------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           层板     993029,993030,993031
2           顶板     311021,311022
3           中侧板    223345,223346,223347,223348
*/

[/code]

[u010009545]

楼上正解

[rings2000]

CREATE FUNCTION f_hb ( @id VARCHAR(10) )
RETURNS VARCHAR(1000)
AS
    BEGIN
        DECLARE @str VARCHAR(1000)
        SET @str = ''
        SELECT  @str = @str + ',' + [pcode]  FROM    [tab2]

        --WHERE   CHARINDEX(',' + CAST(  pid AS VARCHAR(256)) + ','  ,   ',' + @id + ','  ) > 0
        where  CAST(  pid AS VARCHAR(256)) =  @id

        RETURN  STUFF(@str,1,1,'')
    END
go 
SELECT  [pid] ,
        [pname] ,
        classid = dbo.f_hb(pid)
FROM    [tab1] 
go 

[rings2000]

if LEN(@s)>0 set @s=stuff(@s,1,1,'') return @s