这样的方法只能使用动态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)