sql server数据库查询语句的问题

Question

有这样一张表

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

求教高手怎么做啊

Answer 1

这样的方法只能使用动态sql，以下sql亲测可用
我问我自己，为什么花一个小时回答你的问题？
给分吧。

DROP TABLE #temp1
DROP TABLE #temp2
CREATE TABLE #temp1
(
[No] INT ,
name VARCHAR(40) ,
age INT
)
INSERT INTO #temp1
VALUES ( 1, '张三', 18 )
INSERT INTO #temp1
VALUES ( 1, '李四', 17 )
INSERT INTO #temp1
VALUES ( 1, '王五', 23 )
INSERT INTO #temp1
VALUES ( 1, '赵六', 40 )
INSERT INTO #temp1
VALUES ( 2, 'Tom', 17 )

INSERT INTO #temp1
VALUES ( 3, 'Bob', 19 )
INSERT INTO #temp1
VALUES ( 3, 'Tony', 36 )
INSERT INTO #temp1
VALUES ( 3, 'Petter', 25 )
--SELECT * FROM #temp1

DECLARE @iCounts INT
SELECT @iCounts = MAX(counts)
FROM ( SELECT [No] ,
COUNT(*) AS counts
FROM #temp1
GROUP BY [No]
) a
PRINT @iCounts

SELECT [No]
INTO #temp2
FROM ( SELECT [No] ,
COUNT(*) AS counts
FROM #temp1
GROUP BY [No]
) a
ORDER BY counts DESC

--SELECT * FROM #temp2

DECLARE @SQL1 VARCHAR(8000)
SET @SQL1 = ''
DECLARE @SQL2 VARCHAR(8000)
SET @SQL2 = ''

DECLARE @SQL3 VARCHAR(8000)
SET @SQL3 = ''

DECLARE @nos INT
DECLARE @nos2 INT
DECLARE @num INT
DECLARE @num1 INT
SET @num1 = 0

DECLARE @name VARCHAR(8000)
DECLARE @age INT

DECLARE @sqlCur VARCHAR(8000)
DECLARE @TableTmp VARCHAR(8000)

DECLARE cur CURSOR
FOR
SELECT [No] AS nos
FROM #temp2

OPEN cur
FETCH NEXT FROM cur INTO @nos
WHILE @@fetch_status = 0
BEGIN
SELECT @nos2 = COUNT(*)
FROM #temp1
WHERE [No] = @nos
PRINT @nos2

    SET @num = 0 

    SET @sqlCur = 'declare c_record cursor global for select name,age from #temp1 where [no] ='''
        + CONVERT(VARCHAR(20), @nos) + ''''
    PRINT @sqlCur
    EXEC (@sqlCur)
    OPEN c_record
    FETCH NEXT FROM c_record INTO @name, @age
    WHILE @@FETCH_STATUS = 0 --fetch语句成功
        BEGIN 
            IF @num < @nos2 
                BEGIN
                    PRINT '@num<@nos2'
                    SET @TableTmp = 'temp' + CONVERT(VARCHAR(10), @nos)
                        + '_' + CONVERT(VARCHAR(10), @num)
                    SET @SQL1 = @SQL1 + 'left outer join #temp1 '
                        + @TableTmp + ' on (a.[no]=' + @TableTmp
                        + '.[no] and ' + @TableTmp + '.name=''' + @name
                        + ''' and ' + @TableTmp + '.[no]='
                        + CONVERT(VARCHAR(10), @nos) + ') '
                    SET @SQL2 = @SQL2 + ',' + @TableTmp + '.name AS name'
                        + CONVERT(VARCHAR(10), @num + 1) + ',' + @TableTmp
                        + '.age as age' + CONVERT(VARCHAR(10), @num + 1)
                END


            SET @num = @num + 1 
            SET @num1 = @num1 + 1 

            FETCH NEXT FROM c_record INTO @name, @age
        END

    CLOSE c_record
    DEALLOCATE c_record

    WHILE @num < @iCounts 
        BEGIN 

            SET @SQL2 = @SQL2 + ','''',0'
            SET @num = @num + 1 
        END


    PRINT @SQL1
    PRINT @SQL2

    IF ( @SQL3 = '' ) 
        BEGIN

            SET @SQL3 = @SQL3 + 'select a.*' + @SQL2 + ' from #temp2 a '
                + @SQL1 + ' where a.[no]=' + CONVERT(VARCHAR(10), @nos)
        END 
    ELSE 
        BEGIN
            SET @SQL3 = @SQL3 + ' union all select a.*' + @SQL2
                + ' from #temp2 a ' + @SQL1 + ' where a.[no]='
                + CONVERT(VARCHAR(10), @nos)
        END 
    SET @SQL1 = ''
    SET @SQL2 = ''

    --SET @SQL1 = @SQL1 + CONVERT(VARCHAR(10), @nos)

    FETCH NEXT FROM cur INTO @nos
END

CLOSE cur
DEALLOCATE cur

PRINT @SQL3

EXEC (@SQL3)