又一道有趣的sql查询题

时间：2010-10-13 来源：DhuXin

列名Id   test        no
    1    hello        1
    1    Jack         2
    1    you          3
    2    My           1
    2    is Lilei     3
    2    name         2
如要得到以下结果：
1 hello Jack you
2 My name is Lilei
请写出sql语句

我写的如下：

DECLARE @tempTable TABLE
(
id INT PRIMARY KEY,
field NVARCHAR(500) NULL
)
DECLARE @result NVARCHAR(500),@temp NVARCHAR(500),@no INT;

DECLARE _cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT t.id,t.test FROM temp t ORDER BY t.id,t.no--表名temp

OPEN _cursor
FETCH NEXT FROM _cursor INTO @no, @temp

WHILE @@FETCH_STATUS = 0
BEGIN

　　IF NOT EXISTS(SELECT * FROM @tempTable WHERE id = @no)
INSERT INTO @tempTable (id,field) VALUES(@no,'')
　　SELECT @result = (SELECT field FROM @tempTable WHERE id = @no)
　　SELECT @result = @result + @temp
　　UPDATE @tempTable SET field = @result WHERE id=@no

　　FETCH NEXT FROM _cursor INTO @no, @temp
END
CLOSE _cursor
DEALLOCATE _cursor

SELECT field FROM @tempTable

相关阅读更多 +