講解數(shù)據(jù)庫(kù)中行級(jí)轉(zhuǎn)換成列級(jí)的實(shí)際應(yīng)用

首先，我們需要建立一個(gè)表,詳細(xì)內(nèi)容請(qǐng)參考下文：
    create table score
    (
     Name varchar(10),
     Subject varchar(10),
     Result int
    )
    go
    -- Insert some data to score table
    insert 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 operator
    SELECT * FROM
    (
     select Name,Subject,Result from score
    ) as X
    PIVOT
    (
     Sum(Result) FOR Subject IN ([Chinese],[Math],[Physic])
    ) AS PVT
    -- useing static SQL. There only are [Chinese],
    [Math],[Physic] values in subject field
    select 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 score
    group 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 X
    set @sql = @sql + ' from score group by Name'
    exec(@sql)