create table score( Name varchar(10), Subject varchar(10), Result int)go -- Insert some data to score tableinsert into score(Name , Subject , Result) values('Jason' , 'Chinese' , 74)insert into score(Name , Subject , Result) values('Jason' , 'Math' , 83)insert into score(Name , Subject , Result) values('Jason' , 'Physic' , 93)insert into score(Name , Subject , Result) values('Bosco' , 'Chinese' , 74)insert into score(Name , Subject , Result) values('Bosco' , 'Math' , 84)insert into score(Name , Subject , Result) values('Bosco' , 'Physic' , 94)go -- useing PIVOT OperatorSELECT * FROM( select Name,Subject,Result from score) as XPIVOT( Sum(Result) FOR Subject IN ([Chinese],[Math],[Physic])) AS PVT -- useing static SQL. There only are [Chinese],[Math],[Physic] values in subject fieldselect Name, sum(case Subject when 'Chinese' then Result else 0 end) as [Chinese], sum(case Subject when 'Math' then Result else 0 end) as [Math], sum(case Subject when 'Physic' then Result else 0 end) as [Physic]from scoregroup by Name -- useing dynamic SQL. There may be some other values in subject field,don't limited to [Chinese],[Math],[Physic]declare @sql varchar(2000)set @sql = 'select Name'select @sql = @sql + ', sum(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'from (select distinct Subject from score) as Xset @sql = @sql + ' from score group by Name'exec(@sql) |
