在使用SQL Server數(shù)據(jù)庫的過程中我們經常會遇到需要將行數(shù)據(jù)和列數(shù)據(jù)相互轉換顯示的問題。對于這個問題SQL Server數(shù)據(jù)庫有專門的內置函數(shù)PIVOT(行轉列)、UNPIVOT(列轉行)可以解決。下面我們就來分析一下這兩個函數(shù)的使用方法。

一、行轉列PIVOT

1、語法

select * from 源表名 as 表別名pivot( 聚合函數(shù)(源表需要聚合顯示的列的字段名)for 源表數(shù)據(jù)中需要轉換為列名的列的字段名 in (轉換后的列名1,轉換后的列名2…))as 表別名

2、舉個例子

先創(chuàng)建一個作為數(shù)據(jù)源的表插入少量數(shù)據(jù)：

create table StudentScores 
(
StudentName varchar(16),
Subject varchar(16),
Score  smallint
)
insert into StudentScores
values('張三','語文',85)
insert into StudentScores
values('張三','數(shù)學',90)
insert into StudentScores
values('張三','英語',86)
insert into StudentScores
values('李四','語文',92)
insert into StudentScores
values('李四','數(shù)學',87)
insert into StudentScores
values('李四','英語',90)

此時 select * from StudentScores 直接查詢結果如下：

下面是我們使用PIVOT函數(shù)轉換后查詢出的結果，先看下代碼：

select * from StudentScores as s  --源數(shù)據(jù)
pivot(max(Score) for Subject in (語文,數(shù)學,英語)) as ss --轉換后的結果數(shù)據(jù)

查詢出的結果如圖：

二、列轉行UNPIVOT

1、語法

select * from 源表名 as 表別名
unpivot(定義由源表數(shù)據(jù)轉換成的列的列名 for 定義由源表字段名轉換成的列的列名 in
(源表字段名1,源表字段名2…)) as 表別名

2、實例

為求直觀直接用上面實例的結果數(shù)據(jù)創(chuàng)建一個新表：

create table StudentGrades
(
StudentName varchar(16),
語文 smallint,
數(shù)學 smallint,
英語 smallint,
)
insert into StudentGrades
values('張三',85,90,86)
insert into StudentGrades
values('李四',92,87,90)

select * from StudentGrades直接查詢結果如下：

使用UNPIVOT函數(shù)進行轉換，代碼如下：

select * from StudentGrades as sg --源數(shù)據(jù)
unpivot(Score for Subject in (語文,數(shù)學,英語)) as cjd --轉換后的結果數(shù)據(jù)

結果如圖：

和PIVOT函數(shù)的實例中源數(shù)據(jù)的表基本相同。當然我們也可以不創(chuàng)建新表直接使用以下代碼查詢出相同的結果：

select * from StudentScores as s 
pivot(max(Score) for Subject in (語文,數(shù)學,英語)) as ss
unpivot(Score for Subject in (語文,數(shù)學,英語)) as cjd

通過上面的例子，我們簡單了解了PIVOT和UNPIVOT這兩個函數(shù)的使用方法，在例子中我們用這兩個函數(shù)實現(xiàn)了數(shù)據(jù)的行列逆轉查詢，雖然UNPIVOT函數(shù)可以將PIVOT函數(shù)轉換后的結果還原成轉換前的樣子，但UNPIVOT 并不完全是 PIVOT 的逆操作。 PIVOT 執(zhí)行聚合，并將多個可能的行合并為輸出中的一行。 UNPIVOT 不重現(xiàn)原始表值表達式的結果，因為行已被合并。 另外，UNPIVOT 輸入中的 NULL 值也在輸出中消失了。 如果值消失，表明在執(zhí)行 PIVOT 操作前，輸入中可能就已存在原始 NULL 值。

到此這篇關于SQL Server行列轉換的實現(xiàn)示例的文章就介紹到這了,更多相關SQL Server行列轉換內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論