我这个游标写的有问题吗？

[woshisqlaihaozhe]

有一个表的结构是这样的：
matlid matname num
a A 2
a B 1
a C 3
b C 1
b E 4
如何显示为表：
matlid type
a A 2 B 1 C 3
b C 1 E 4

我按下面写怎么运行不出来呀，高手指点以下，谢谢了！

select rtrim(partnum)+ltrim(revision) as matlid,matname,count(*) as num
into #temp1
from prodpress
where type='C' or type='w'
group by rtrim(partnum)+ltrim(revision),matname
order by rtrim(partnum)+ltrim(revision),matname

select rtrim(partnum)+ltrim(revision) as matlid,matname,count(*) as num
into #temp2
from prodpress
where type='p'
group by rtrim(partnum)+ltrim(revision),matname
order by rtrim(partnum)+ltrim(revision),matname
---select * from #temp1

declare
@bdate varchar(10),
@edate varchar(10)
set @bdate='2007-02-01'
set @edate='2008-02-09'

select a.productid as matlid,b.areapcs as sfpcs,c.bigpiece as bigpiece ,c.lpiece as lpiece ,c.llpiece as llpiece,
convert(varchar(200),'') as PP,convert(varchar(200),'') as jb
into #tem
from ecm03010 a,sal01010 b,prodbasic c
where a.admit_date between @bdate and @edate
and a.productid=b.matlid
and a.productid=rtrim(c.partnum)+ltrim(c.revision)
and c.layer=0

declare @mat varchar(20),@Pres_Mat_Code varchar(40),@num int

DECLARE tem CURSOR FOR
SELECT matlid
FROM #tem

OPEN tem
FETCH NEXT FROM tem INTO @mat
while @@fetch_status=0

declare tem1 cursor for
select matname,num
from #temp1
where @mat=matlid
group by matname,num

open tem1
FETCH NEXT FROM tem1 INTO @Pres_Mat_Code,@num
while @@fetch_status=0
begin
update #tem set PP=rtrim(PP)+' '+ltrim(@Pres_Mat_Code)+' '+ltrim(@num) where matlid=@mat
FETCH NEXT FROM tem1 INTO @Pres_Mat_Code,@num
end

CLOSE tem1
DEALLOCATE tem1

FETCH NEXT FROM tem INTO @mat

CLOSE tem
DEALLOCATE tem

select * from #tem

[wzy_love_sly]

create table tb(matlid varchar(20),matname varchar(10),num int)
insert into tb select 'a','A',2
insert into tb select 'a','B',1
insert into tb select 'a','C',2
insert into tb select 'b','C',1
insert into tb select 'b','E',4

CREATE FUNCTION dbo.f_str(@matlid varchar(10))
RETURNS varchar(100)
AS
BEGIN
	DECLARE @re varchar(100)
	SET @re=''
	SELECT @re=@re+' '+CAST(matname as varchar)+' '+CAST(num as varchar)
	FROM tb
	WHERE matlid=@matlid
	RETURN(STUFF(@re,1,1,''))
END

select matlid,dbo.f_str(matlid) from tb
group by matlid

a A 2 B 1 C 2
b C 1 E 4

[woshisqlaihaozhe]

谢谢这位兄弟~

[wzy_love_sly]

::

[liangCK]

--各种字符串分函数

--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))

--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY  col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
	IF @col1=@col1_old
		SELECT @s=@s+','+CAST(@col2 as varchar)
	ELSE
	BEGIN
		INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
		SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
	END
	FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO


/*==============================================*/


--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO

--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
	DECLARE @re varchar(100)
	SET @re=''
	SELECT @re=@re+','+CAST(col2 as varchar)
	FROM tb
	WHERE col1=@col1
	RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO

/*==============================================*/


--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
SELECT col1,col2=CAST(col2 as varchar(100)) 
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET 
	@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
	@col1=col1,
	col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO


/*==============================================*/

--3.3.4.1 每组 <=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
	col2=CAST(MIN(col2) as varchar)
		+CASE 
			WHEN COUNT(*)=1 THEN ''
			ELSE ','+CAST(MAX(col2) as varchar)
		END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2      
---------- ----------
a          1,2
b          1,2
c          3
--*/

--3.3.4.2 每组 <=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
	col2=CAST(MIN(col2) as varchar)
		+CASE 
			WHEN COUNT(*)=3 THEN ','
				+CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
			ELSE ''
		END
		+CASE 
			WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
			ELSE ''
		END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
--*/
GO