SQL Server基礎(chǔ)之行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)

use [test1]
go

create table [dbo].[student](
  [id] [int] identity(1,1) not null,
  [name] [nvarchar](50) null,
  [project] [nvarchar](50) null,
  [score] [int] null,
 constraint [pk_student] primary key clustered 
(
  [id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]
go

insert into test1.dbo.student(name,project,score)
values('張三','android','60'),
   ('張三','ios','70'),
   ('張三','html5','55'),
   ('張三','.net','100'),
   ('李四','android','60'),
   ('李四','ios','75'),
   ('李四','html5','90'),
   ('李四','.net','100');

select column_name,
<aggregation function>(<case when expression>) 
from database.schema.table
group by column_name

select name,
max(case project when 'android' then score end) as '安卓',
max(case project when 'ios' then score end) as '蘋果',
max(case project when 'html5' then score end) as 'html5',
max(case project when '.net' then score end) as '.net'
from [test1].[dbo].[student]
group by name

select <non-pivoted column>, 
  [first pivoted column] as <column name>, 
  [second pivoted column] as <column name>, 
  ... 
  [last pivoted column] as <column name> 
from 
  (<select query that produces the data>)  
  as <alias for the source query> 
pivot 
( 
  <aggregation function>(<column being aggregated>) 
for  
[<column that contains the values that will become column headers>]  
  in ( [first pivoted column], [second pivoted column], 
  ... [last pivoted column]) 
) as <alias for the pivot table> 
<optional order by clause>;

select b.Name,b.[android],b.[ios],b.[html5],b.[.net] 
from 
(select Name,Project,Score from [test1].[dbo].[student])
as a
pivot
(
  max(Score)
  for Project in ([android],[ios],[html5],[.net])
) 
as b
order by b.name desc