
------解决思路----------------------
CASE WHEN,然后聚合一下。
------解决思路----------------------
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
------解决思路----------------------
上面的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
------解决思路----------------------
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)
------解决思路----------------------
#3樓表名弄錯了,修正一下。
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