34,838
社区成员




--动态
DECLARE @SQL VARCHAR(8000)
DECLARE @M INT,@I VARCHAR(10)
SET @SQL='SELECT NAME'
SELECT TOP 1 @M=COUNT(1)FROM Table1 GROUP BY [NAME] ORDER BY COUNT(1) DESC
IF @M>4 SET @M=4
SET @I=1
WHILE @I<=@M
SELECT @SQL=@SQL+',MAX(CASE WHEN RN='+@I+' THEN DIAGNOSIS END)[D'+@I+']',@I=@I+1
SET @SQL=@SQL+' FROM(SELECT *,ROW_NUMBER()OVER(PARTITION BY [NAME] ORDER BY GETDATE())RN'
+' FROM Table1)T GROUP BY [NAME]'
--PRINT @SQL
EXEC(@SQL)
不足四个的情况,最多是多少则显示多少--动态
DECLARE @SQL VARCHAR(8000)
DECLARE @M INT,@I VARCHAR(10)
SET @SQL='SELECT NAME'
SET @M=4
SET @I=1
WHILE @I<=@M
SELECT @SQL=@SQL+',MAX(CASE WHEN RN='+@I+' THEN DIAGNOSIS END)[D'+@I+']',@I=@I+1
SET @SQL=@SQL+' FROM(SELECT *,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY GETDATE())RN'
+' FROM Table1)T GROUP BY NAME'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT NAME
,MAX(CASE WHEN RN=1 THEN DIAGNOSIS END)[D1]
,MAX(CASE WHEN RN=2 THEN DIAGNOSIS END)[D2]
,MAX(CASE WHEN RN=3 THEN DIAGNOSIS END)[D3]
,MAX(CASE WHEN RN=4 THEN DIAGNOSIS END)[D4]
FROM(SELECT *,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY GETDATE())RN FROM Table1)T
GROUP BY NAME