DECLARE @Str VARCHAR(2000),
@name VARCHAR(50),
@dw VARCHAR(50),
@xw VARCHAR(50)
SET @Str=''
IF EXISTS(SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID=OBJECT_ID('TEMPDB.DBO.#Result'))
DROP TABLE #Result
SELECT DISTINCT [name],[name] AS dwxw INTO #Result FROM Test ORDER BY [name]
ALTER TABLE #Result
ALTER COLUMN dwxw VARCHAR(2000) NULL
DECLARE name_cursor CURSOR FOR SELECT DISTINCT [name] FROM Test ORDER BY [name]
OPEN name_cursor
FETCH NEXT FROM name_cursor INTO @name
WHILE(@@FETCH_STATUS=0) -- 对name循环
BEGIN
DECLARE temp_cursor CURSOR FOR SELECT DISTINCT dw FROM Test WHERE [name]=@name ORDER BY dw
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @dw
WHILE(@@FETCH_STATUS=0) -- 对DW循环
BEGIN
SET @Str=@Str + ',' + @dw + ' ' -- 不同以逗号分开
DECLARE Test_cursor CURSOR FOR SELECT xw FROM Test WHERE [name]=@name AND dw=@dw ORDER BY xw
OPEN Test_cursor
FETCH NEXT FROM Test_cursor INTO @xw
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @Str = @Str + @xw + '.' -- 同dw中zw 以'.'连接
FETCH NEXT FROM Test_cursor INTO @xw
END
CLOSE Test_cursor
DEALLOCATE Test_cursor
--删除最后一个 '.'号
IF LEN(@Str)>0
SET @Str=SUBSTRING(@Str,1,LEN(@Str)-1)
FETCH NEXT FROM temp_cursor INTO @dw
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
-- 更新结果集(##Result)中[name]=@name的记录
--删除第一个','号
IF LEN(@Str)>0
SET @Str=SUBSTRING(@Str,2,LEN(@Str))
UPDATE #Result SET dwxw=@Str WHERE [name]=@name
FETCH NEXT FROM name_cursor INTO @name
END
CLOSE name_cursor
DEALLOCATE name_cursor
SELECT * FROM #Result
我这个方法效率不是很高,但能实现你要的效果
等有时间,看看还有没有其他方法
