27,580
社区成员
发帖
与我相关
我的任务
分享
select
Sum(case when name='A' then age else null end) as Aage,
Max(case when name='A' then sex else null end) as Asex,
Sum(case when name='A' then kg else null end) as Akg,
Sum(case when name='B' then age else null end) as Bage,
Max(case when name='B' then sex else null end) as Bsex,
Sum(case when name='B' then kg else null end) as Bkg,
Sum(case when name='C' then age else null end) as Cage,
Max(case when name='C' then sex else null end) as Csex,
Sum(case when name='C' then kg else null end) as Ckg
from Student
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL=''
SELECT @SQL=@SQL+',MAX(CASE WHEN name='''+name+''' THEN age END)['+name+'age]'
+',MAX(CASE WHEN name='''+name+''' THEN sex END)['+name+'sex]'
+',MAX(CASE WHEN name='''+name+''' THEN kg END)['+name+'kg]'
FROM student GROUP BY name
SET @SQL='SELECT '+STUFF(@SQL,1,1,'')+'FROM student'
--PRINT @SQL
EXEC(@SQL)
动态方式
select
Sum(case when name='A' then age else null end) as Aage,
Max(case when name='A' then sex else null end) as Asex,
Sum(case when name='A' then kg else null end) as Akg,
Sum(case when name='B' then age else null end) as Bage,
Max(case when name='B' then sex else null end) as Bsex,
Sum(case when name='B' then kg else null end) as Bkg,
Sum(case when name='C' then age else null end) as Cage,
Max(case when name='C' then sex else null end) as Csex,
Sum(case when name='C' then kg else null end) as Ckg
from t1
select
Sum(case when name='A' then age else null end) as Aage,
Sum(case when name='A' then sex else null end) as Asex,
Sum(case when name='A' then kg else null end) as Akg,
Sum(case when name='B' then age else null end) as Bage,
Sum(case when name='B' then sex else null end) as Bsex,
Sum(case when name='B' then kg else null end) as Bkg,
Sum(case when name='C' then age else null end) as Cage,
Sum(case when name='C' then sex else null end) as Csex,
Sum(case when name='C' then kg else null end) as Ckg
from Student