sql server数据库查询语句 不会写 求大佬帮助

[sinat_38913556]

NO name age
1 张三 18
1 李四 17
1 王五 23
1 赵六 40
2 Tom 17
3 Bob 19
3 Tony 36
3 Petter 25

现在想查询出这样的内容

NO name1 age1 name2 age2 name3 age3 name4 age4
1 张三 18 李四 17 王五 23 赵六 40
2 Tom 17
3 Bob 19 Tony 36 Petter 25

求教高手怎么做啊

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t([NO] INT,NAME NVARCHAR(20), age INT)
GO
INSERT INTO t
SELECT 1,'张三','18'
UNION ALL SELECT 1,'李四','17'
UNION ALL SELECT 1,'王五','23'
UNION ALL SELECT 1,'赵六','40'
UNION ALL SELECT 2,'Tom','17'
UNION ALL SELECT 3,'Bob','19'
UNION ALL SELECT 3,'Tony','36'
UNION ALL SELECT 3,'Petter','25'
-------------- 以上为测试数据 --------------------

--建立动态SQL
DECLARE @sql NVARCHAR(MAX)
SELECT @sql=ISNULL(@sql,'')+',MAX(CASE WHEN rid='+LTRIM(sv.number)+' THEN NAME ELSE NULL END) AS name'+LTRIM(sv.number)+'
,MAX(CASE WHEN rid='+LTRIM(sv.number)+' THEN AGE ELSE NULL END) AS age'+LTRIM(sv.number)+'
'
FROM
MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 
(
	SELECT MAX(cnt) FROM (SELECT COUNT(No) AS cnt FROM t GROUP BY No) AS t
)
SET @sql='
;WITH cte AS (
	SELECT ROW_NUMBER() OVER (PARTITION BY no ORDER BY (SELECT 1)) AS rid,* FROM t
)
SELECT NO'+@sql+'
FROM cte AS a
GROUP BY NO
'
--输出动态SQL
PRINT @sql
/*
;WITH cte AS (
	SELECT ROW_NUMBER() OVER (PARTITION BY no ORDER BY (SELECT 1)) AS rid,* FROM t
)
SELECT NO,MAX(CASE WHEN rid=1 THEN NAME ELSE NULL END) AS name1
,MAX(CASE WHEN rid=1 THEN AGE ELSE NULL END) AS age1
,MAX(CASE WHEN rid=2 THEN NAME ELSE NULL END) AS name2
,MAX(CASE WHEN rid=2 THEN AGE ELSE NULL END) AS age2
,MAX(CASE WHEN rid=3 THEN NAME ELSE NULL END) AS name3
,MAX(CASE WHEN rid=3 THEN AGE ELSE NULL END) AS age3
,MAX(CASE WHEN rid=4 THEN NAME ELSE NULL END) AS name4
,MAX(CASE WHEN rid=4 THEN AGE ELSE NULL END) AS age4
FROM cte AS a
GROUP BY NO
*/
--执行
EXEC (@sql)
/*
NO	name1	age1	name2	age2	name3	age3	name4	age4
1	张三	18	    李四	17	    王五	23	    赵六	40
2	Tom	    17	    NULL	NULL	NULL	NULL	NULL	NULL
3	Bob	    19	    Tony	36	    Petter	25	    NULL	NULL
*/