MSSQL PIVOT 行转列

 1 CREATE TABLE [dbo].[user](
 2     [id] [int] IDENTITY(1,1) NOT NULL,
 3     [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
 4 )
 5 
 6 
 7 CREATE TABLE [dbo].[score](
 8     [user_id] [int] NULL,
 9     [kemu] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
10     [fengshu] [float] NULL
11 )
12 
13 
14

方法一:pivot

 SELECT name,[yuwen],[shuxue],isnull([yingyu],0) FROM dbo.score inner join dbo.[user] on id=user_id PIVOT (
15     SUM(fengshu) FOR [kemu] IN ([yuwen],[shuxue],[yingyu])
16 ) AS T

方法二:case when

select name,
       sum(case kemu when 'yuwen' then (fengshu) else 0 end ) as 'yuwen',
       sum(case kemu when 'shuxue' then (fengshu) else 0 end ) as 'shuxue',
       sum(case kemu when 'yingyu' then (fengshu) else 0 end ) as 'yingyu'
from score inner join dbo.[user] on id=user_id group by name