27,580
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(max)
set @sql='' --初始化变量@sql
select @sql=@sql+','+grade from #T group by GRADE --变量多值赋值
set @sql=stuff(@sql,1,1,'') --去掉首个‘,‘
set @sql='SELECT * FROM #T PIVOT (COUNT(NAME) FOR GRADE IN ('+@sql+'))B'
exec(@sql)
CREATE TABLE #T
(CLASS VARCHAR(10),
NAME VARCHAR(20),
GRADE VARCHAR(10))
INSERT INTO #T
SELECT '1','TOM','A' UNION ALL
SELECT '1','JACK','A' UNION ALL
SELECT '1','JERRY','B' UNION ALL
SELECT '1','JANE','A' UNION ALL
SELECT '2','ROSE','C' UNION ALL
SELECT '2','FRANK','C' UNION ALL
SELECT '3','JERRY','Q' UNION ALL
SELECT '3','ZOE','C' UNION ALL
SELECT '3','HENRY','A'
SELECT *
FROM #T
PIVOT (COUNT(NAME) FOR GRADE IN ([A],[B],[C],[Q])) B