如何写这个视图
这是表的数据
ph name
1 xxj
2 zdx
3 hao
1 tang
3 xin
4 wan
1 fore
通过一句语句咨询后变成一个视图如下表
ph name
1 xxj,tang,fore
2 zdx
3 hao,xin
4 wan
相同的用逗号阁开
create procedure list
as
DECLARE @count INT
DECLARE @ph INT
DECLARE @name VARCHAR(255)
CREATE TABLE #temp
(
ph int primary key,
names varchar(255)
)
SET @count=1
WHILE @count<=(Select Max(ph) from test1)
BEGIN
INSERT INTO #temp
VALUES (@count,'')
SET @count=@count+1
END
DECLARE CursorTest Cursor For
SELECT ph,name
FROM test1
OPEN CursorTest
Fetch Next From CursorTest
Into @ph,@name
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE #temp
set
names=names+ ','+ @name
WHERE
ph = @ph
Fetch Next From CursorTest
Into @ph,@name
END
CLOSE CursorTest
DEALLOCATE CursorTest
SELECT ph,(select right(names,len(names)-1))
FROM #temp
先运行一下,然后执行exec list就可以了,原有数据表名我随便设成了test1,你自己看着改吧
[此贴子已经被作者于2006-2-18 13:11:34编辑过]