34,576
社区成员
发帖
与我相关
我的任务
分享
--測試數據
SELECT * INTO #T FROM (
SELECT 'S'TYP,'A' A,'B' B,'C' C
UNION SELECT 'S','A','2','3'
)Z
SELECT *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) ID INTO #T1 FROM #T
DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT TYP'
SELECT @SQL=@SQL+',MAX(CASE WHEN ID='+CONVERT(VARCHAR,ID)+' THEN A ELSE NULL END) [A'+CONVERT(VARCHAR,ID)+'],
MAX(CASE WHEN ID='+CONVERT(VARCHAR,ID)+' THEN B ELSE NULL END) [A'+CONVERT(VARCHAR,ID)+'],
MAX(CASE WHEN ID='+CONVERT(VARCHAR,ID)+' THEN C ELSE NULL END) [A'+CONVERT(VARCHAR,ID)+']'
FROM #T1
GROUP BY ID
ORDER BY ID
SET @SQL=@SQL+' FROM #T1 GROUP BY TYP'
EXEC(@SQL)
DROP TABLE #T
DROP TABLE #T1
/*
TYP A1 A1 A1 A2 A2 A2
---- ---- ---- ---- ---- ---- ----
S A B C A 2 3
*/