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

更新時間：2019年08月06日 11:36:04 作者：小子pk了

這篇文章主要給大家介紹了關(guān)于SQL Server基礎(chǔ)之行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學(xué)習(xí)或者使用SQL Server具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧

準(zhǔn)備工作

創(chuàng)建表

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

插入數(shù)據(jù)

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');

使用Case When和聚合函數(shù)進行行專列

語法

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

語法解析

column_name

數(shù)據(jù)列列名

aggregation function

聚合函數(shù)，常見的有：sum,max,min,avg,count等。

case when expression

case when表達式

示例

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

示例結(jié)果

轉(zhuǎn)換前

轉(zhuǎn)換后

使用PIVOT進行行專列

PIVOT通過將表達式中一列中的唯一值轉(zhuǎn)換為輸出中的多個列來旋轉(zhuǎn)表值表達式。并PIVOT在最終輸出中需要的任何剩余列值上運行聚合，PIVOT提供比一系列復(fù)雜的SELECT...CASE語句指定的語法更為簡單和可讀的語法，PIVOT執(zhí)行聚合并將可能的多行合并到輸出中的單個行中。

語法

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>;

語法解析

<non-pivoted column>

非聚合列。

[first pivoted column]

第一列列名。

[second pivoted column]

第二列列名。

[last pivoted column]

最后一列列名。

數(shù)據(jù)子表。

表別名。

聚合函數(shù)。

聚合函數(shù)列，用于輸出值列，最終輸出中返回的列（稱為分組列）將對其進行分組。

[<column that contains the values that will become column headers>]

轉(zhuǎn)換列，此列返回的唯一值將成為最終結(jié)果集中的字段。

[first pivoted column], [second pivoted column], ... [last pivoted column]

數(shù)據(jù)行中每一行行要轉(zhuǎn)換的列名。

排序規(guī)則。

示例

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

示例結(jié)果

轉(zhuǎn)換前