SQL 橫轉豎 、豎專橫 （轉載）

来源:https://www.cnblogs.com/hao-1234-1234/archive/2018/11/13/8571673.html
-Advertisement-
Play Games

/*普通行列轉換 假設有張學生成績表(tb)如下:Name Subject Result張三 語文 74張三 數學 83張三 物理 93李四 語文 74李四 數學 84李四 物理 94*/ /*想變成 姓名 語文 數學 物理 李四 74 84 94張三 74 83 93*/createtable t ...


普通行列轉換 
問題：假設有張學生成績表(tb)如下: 
姓名 課程 分數 
張三 語文 74 
張三 數學 83 
張三 物理 93 
李四 語文 74 
李四 數學 84 
李四 物理 94 
想變成(得到如下結果)： 
姓名 語文 數學 物理 
---- ---- ---- ---- 
李四 74  84  94 
張三 74  83  93 
------------------- 
*/ 

create table tb(姓名 varchar(10) , 課程 varchar(10) , 分數 int) 
insert into tb values('張三' , '語文' , 74) 
insert into tb values('張三' , '數學' , 83) 
insert into tb values('張三' , '物理' , 93) 
insert into tb values('李四' , '語文' , 74) 
insert into tb values('李四' , '數學' , 84) 
insert into tb values('李四' , '物理' , 94) 
go 

--SQL SERVER 2000 靜態SQL,指課程只有語文、數學、物理這三門課程。(以下同) 
select 姓名 as 姓名 , 
  max(case 課程 when '語文' then 分數 else 0 end) 語文, 
  max(case 課程 when '數學' then 分數 else 0 end) 數學, 
  max(case 課程 when '物理' then 分數 else 0 end) 物理 
from tb 
group by 姓名 

--SQL SERVER 2000 動態SQL,指課程不止語文、數學、物理這三門課程。(以下同) 
declare @sql varchar(8000) 
set @sql = 'select 姓名 ' 
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']' 
from (select distinct 課程 from tb) as a 
set @sql = @sql + ' from tb group by 姓名' 
exec(@sql) 

--SQL SERVER 2005 靜態SQL。 
select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理)) b 

--SQL SERVER 2005 動態SQL。 
declare @sql varchar(8000) 
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程 
exec ('select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b') 

--------------------------------- 

/* 
問題：在上述結果的基礎上加平均分，總分，得到如下結果： 
姓名 語文 數學 物理 平均分 總分 
---- ---- ---- ---- ------ ---- 
李四 74  84  94  84.00  252 
張三 74  83  93  83.33  250 
*/ 

--SQL SERVER 2000 靜態SQL。 
select 姓名 姓名, 
  max(case 課程 when '語文' then 分數 else 0 end) 語文, 
  max(case 課程 when '數學' then 分數 else 0 end) 數學, 
  max(case 課程 when '物理' then 分數 else 0 end) 物理, 
  cast(avg(分數*1.0) as decimal(18,2)) 平均分, 
  sum(分數) 總分 
from tb 
group by 姓名 

--SQL SERVER 2000 動態SQL。 
declare @sql varchar(8000) 
set @sql = 'select 姓名 ' 
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']' 
from (select distinct 課程 from tb) as a 
set @sql = @sql + ' , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名' 
exec(@sql) 

--SQL SERVER 2005 靜態SQL。 
select m.* , n.平均分 , n.總分 from 
(select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理)) b) m, 
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n 
where m.姓名 = n.姓名 

--SQL SERVER 2005 動態SQL。 
declare @sql varchar(8000) 
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程 
exec ('select m.* , n.平均分 , n.總分 from 
(select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n 
where m.姓名 = n.姓名') 

drop table tb    

------------------ 
------------------ 

/* 
問題：如果上述兩表互相換一下：即表結構和數據為： 
姓名 語文 數學 物理 
張三 74　　83　　93 
李四 74　　84　　94 
想變成(得到如下結果)： 
姓名 課程 分數 
---- ---- ---- 
李四 語文 74 
李四 數學 84 
李四 物理 94 
張三 語文 74 
張三 數學 83 
張三 物理 93 
-------------- 
*/ 

create table tb(姓名 varchar(10) , 語文 int , 數學 int , 物理 int) 
insert into tb values('張三',74,83,93) 
insert into tb values('李四',74,84,94) 
go 

--SQL SERVER 2000 靜態SQL。 
select * from 
( 
select 姓名 , 課程 = '語文' , 分數 = 語文 from tb 
union all 
select 姓名 , 課程 = '數學' , 分數 = 數學 from tb 
union all 
select 姓名 , 課程 = '物理' , 分數 = 物理 from tb 
) t 
order by 姓名 , case 課程 when '語文' then 1 when '數學' then 2 when '物理' then 3 end 

--SQL SERVER 2000 動態SQL。 
--調用系統表動態生態。 
declare @sql varchar(8000) 
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [課程] = ' + quotename(Name , '''') + ' , [分數] = ' + quotename(Name) + ' from tb' 
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名為姓名的其它列 
order by colid asc 
exec(@sql + ' order by 姓名 ') 

--SQL SERVER 2005 動態SQL。 
select 姓名 , 課程 , 分數 from tb unpivot (分數 for 課程 in([語文] , [數學] , [物理])) t 

--SQL SERVER 2005 動態SQL，同SQL SERVER 2000 動態SQL。 

-------------------- 
/* 
問題：在上述的結果上加個平均分，總分，得到如下結果： 
姓名 課程  分數 
---- ------ ------ 
李四 語文  74.00 
李四 數學  84.00 
李四 物理  94.00 
李四 平均分 84.00 
李四 總分  252.00 
張三 語文  74.00 
張三 數學  83.00 
張三 物理  93.00 
張三 平均分 83.33 
張三 總分  250.00 
------------------ 
*/ 

select * from 
( 
select 姓名 as 姓名 , 課程 = '語文' , 分數 = 語文 from tb 
union all 
select 姓名 as 姓名 , 課程 = '數學' , 分數 = 數學 from tb 
union all 
select 姓名 as 姓名 , 課程 = '物理' , 分數 = 物理 from tb 
union all 
select 姓名 as 姓名 , 課程 = '平均分' , 分數 = cast((語文 + 數學 + 物理)*1.0/3 as decimal(18,2)) from tb 
union all 
select 姓名 as 姓名 , 課程 = '總分' , 分數 = 語文 + 數學 + 物理 from tb 
) t 
order by 姓名 , case 課程 when '語文' then 1 when '數學' then 2 when '物理' then 3 when '平均分' then 4 when '總分' then 5 end 

drop table tb

 

 

 

/*
普通行列轉換


假設有張學生成績表(tb)如下:
Name Subject Result
張三 語文　　74
張三 數學　　83
張三 物理　　93
李四 語文　　74
李四 數學　　84
李四 物理　　94
*/

-------------------------------------------------------------------------
/*
想變成 
姓名         語文        數學        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
張三         74          83          93
*/

createtable tb
(
   Name    varchar(10) ,
   Subject varchar(10) ,
   Result  int
)

insertinto tb(Name , Subject , Result) values('張三' , '語文' , 74)
insertinto tb(Name , Subject , Result) values('張三' , '數學' , 83)
insertinto tb(Name , Subject , Result) values('張三' , '物理' , 93)
insertinto tb(Name , Subject , Result) values('李四' , '語文' , 74)
insertinto tb(Name , Subject , Result) values('李四' , '數學' , 84)
insertinto tb(Name , Subject , Result) values('李四' , '物理' , 94)
go

--靜態SQL,指subject只有語文、數學、物理這三門課程。
select name 姓名,
  max(case subject when'語文'then result else0end) 語文,
  max(case subject when'數學'then result else0end) 數學,
  max(case subject when'物理'then result else0end) 物理
from tb
groupby name
/*
姓名         語文        數學        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
張三         74          83          93
*/

--動態SQL,指subject不止語文、數學、物理這三門課程。
declare@sqlvarchar(8000)
set@sql='select Name as '+'姓名'
select@sql=@sql+' , max(case Subject when '''+ Subject +''' then Result else 0 end) ['+ Subject +']'
from (selectdistinct Subject from tb) as a
set@sql=@sql+' from tb group by name'
exec(@sql) 
/*
姓名         數學        物理        語文          
---------- ----------- ----------- ----------- 
李四         84          94          74
張三         83          93          74
*/

-------------------------------------------------------------------
/*加個平均分，總分
姓名         語文        數學        物理        平均分                總分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
張三         74          83          93          83.33                250
*/

--靜態SQL,指subject只有語文、數學、物理這三門課程。
select name 姓名,
  max(case subject when'語文'then result else0end) 語文,
  max(case subject when'數學'then result else0end) 數學,
  max(case subject when'物理'then result else0end) 物理,
  cast(avg(result*1.0) asdecimal(18,2)) 平均分,
  sum(result) 總分
from tb
groupby name
/*
姓名         語文        數學        物理        平均分                總分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
張三         74          83          93          83.33                250
*/

--動態SQL,指subject不止語文、數學、物理這三門課程。
declare@sql1varchar(8000)
set@sql1='select Name as '+'姓名'
select@sql1=@sql1+' , max(case Subject when '''+ Subject +''' then Result else 0 end) ['+ Subject +']'
from (selectdistinct Subject from tb) as a
set@sql1=@sql1+' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 總分 from tb group by name'
exec(@sql1) 
/*
姓名         數學        物理        語文        平均分                總分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         84          94          74          84.00                252
張三         83          93          74          83.33                250
*/

droptable tb    

---------------------------------------------------------
---------------------------------------------------------
/*
如果上述兩表互相換一下：即

姓名 語文 數學 物理
張三 74　　83　　93
李四 74　　84　　94

想變成 
Name       Subject Result      
---------- ------- ----------- 
李四         語文      74
李四         數學      84
李四         物理      94
張三         語文      74
張三         數學      83
張三         物理      93
*/

createtable tb1
(
   姓名 varchar(10) ,
   語文 int ,
   數學 int ,
   物理 int
)

insertinto tb1(姓名 , 語文 , 數學 , 物理) values('張三',74,83,93)
insertinto tb1(姓名 , 語文 , 數學 , 物理) values('李四',74,84,94)

select*from
(
  select 姓名 as Name , Subject ='語文' , Result = 語文 from tb1 
  unionall
  select 姓名 as Name , Subject ='數學' , Result = 數學 from tb1
  unionall
  select 姓名 as Name , Subject ='物理' , Result = 物理 from tb1
) t
orderby name , case Subject when'語文'then1when'數學'then2when'物理'then3when'總分'then4end

--------------------------------------------------------------------
/*加個平均分,總分
Name       Subject     Result               
---------- -------    -------------------- 
李四         語文      74.00
李四         數學      84.00
李四         物理      94.00
李四         平均分    84.00
李四         總分      252.00
張三         語文      74.00
張三         數學      83.00
張三         物理      93.00
張三         平均分    83.33
張三         總分      250.00
*/

select*from
(
  select 姓名 as Name , Subject ='語文' , Result = 語文 from tb1 
  unionall
  select 姓名 as Name , Subject ='數學' , Result = 數學 from tb1
  unionall
  select 姓名 as Name , Subject ='物理' , Result = 物理 from tb1
  unionall
  select 姓名 as Name , Subject ='平均分' , Result =cast((語文 + 數學 + 物理)*1.0/3asdecimal(18,2)) from tb1
  unionall
  select 姓名 as Name , Subject ='總分' , Result = 語文 + 數學 + 物理 from tb1
) t
orderby name , case Subject when'語文'then1when'數學'then2when'物理'then3when'平均分'then4when'總分'then5end

droptable tb1

 

轉載來源:https://www.cnblogs.com/luowende2012/archive/2012/07/19/2599436.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Java集合類源碼解析：AbstractMap
    [toc] 引言 今天學習一個Java集合的一個抽象類 AbstractMap ，AbstractMap 是 Map 介面的 實現類之一，也是HashMap、TreeMap、ConcurrentHashMap 等的父類，它提供了 Map 介面中方法的基本實現（關於Map介面有疑惑的同學可參考 "Ja ...
  • Java集合類根介面：Collection 和 Map
    前言 在前文中我們瞭解了幾種常見的數據結構，這些數據結構有著各自的應用場景，並且被廣泛的應用於編程語言中，其中，Java中的集合類就是基於這些數據結構為基礎。 Java的集合類是一些非常實用的工具類，主要用於存儲和裝載數據 (包括對象)，因此，Java的集合類也被成為容器。在Java中，所有的集合類 ...
  • python模塊導入細節
    python模塊導入細節 官方手冊：https://docs.python.org/3/tutorial/modules.html 可執行文件和模塊 python源代碼文件按照功能可以分為兩種類型： 1. 用於執行的可執行程式文件 2. 不用與執行，僅用於被其它python源碼文件導入的模塊文件 例 ...
  • 小飛俠帶你精通Python網路編程系列04-Python內置的數據類型
    在Python中有以下幾種標準的內置數據類型： 1.None: The Null object--空對象 None是Python的特殊類型，表示一個空對象，值為None2.Numerics（數值）: int-整數, long-長整數, float-浮點數, complex-複數, and bool- ...
  • Java開發筆記（二十三）數組工具Arrays
    數組作為一種組合形式的數據類型，必然要求提供一些處理數組的簡便辦法，包括數組比較、數組複製、數組排序等等。為此Java專門設計了Arrays工具，該工具包含了幾個常用方法，方便程式員對數組進行加工操作。Arrays工具的方法說明如下： 下麵分別對以上的四個數組處理方法進行介紹： 1、Arrays.e ...
  • Spring Security Oauth2 示例
    Spring Security Oauth2 示例，基於SpringBoot搭建授權服務和資源服務 ...
  • Java中transient關鍵字的作用
    Java的serialization提供了一種持久化對象實例的機制。當持久化對象時，可能有一個特殊的對象數據成員，我們不想用serialization機制來保存它。 為了在一個特定對象的一個域上關閉serialization，可以在這個域前加上關鍵字transient。 當一個對象被序列化的時候，t ...
  • asp.net core 的用戶註冊功能——Identity上手
    首先請using這個類庫。 這個類庫老牛逼了，首先是包含了一個IdentityUser類。我們可以自己寫一個User類繼承IdentityUser，這樣一來，很多屬性我就不用手動建了。如你所見，我的User沒有Emai這個欄位，但是IdentityUser有這個欄位。 如何把用戶發來的數據變成一個U ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類