sqlserver行轉列和列轉行

1、1 ： 行轉列子查詢，獲取一定數據集結果SELECT objid,action,count(1) AS count FROM T_MyAttention WHERE objidIN(SELECT TOP 10 objid FROM T_MyAttention tma GROUP BY objid ORDER BYcount(1) DESC)GROUP BY objid,action下面用 行轉列語法獲取最終結果select *from(SELECT objid,action,count(1) AS count FROM T_MyAttention WHERE objidIN(SELECT TO

2、P 10 objid FROM T_MyAttention tma GROUP BY objid ORDER BYcount(1) DESC)GROUP BY objid,action) tpivot ( sum(count) forin (1,2,3,4) as ourpivot格率:泊卮obfid| acti.counl1071050011 31_2_107105CD1141111527701112+1115277011 31111S27701141 I_S_1 而 99776112了1209977011311267422011153132132CCH113101321320D113111

3、132162201112121372393D1113131410844011131414598510111S151466088011111B14E6O86D1131171460 卸 141ohjid1_j2 I2A_|11 (J7W5OO11NULLNULL1i i211152770112WULL1i312099770112MULL1NULL4_12S7433D115NULLMULLNULL132132m 13NULL1NULLULLFULLNULL_7ULLMULLNULLULLHULLNULL314598 JI

4、0116NULLNULLNULL10i14GO9O111MULL11微軟官方的圖:2 :列轉行怎么把一條記錄拆分成幾條記錄?UserNo.ABC1121342412RESULT:422516UserNo.TypeNum11A2111B3411C2412A4212B2512C16declare t table(usser int ,no int ,a int,b int, c int)insert into t select 1,1,21,34,24union all select 1,2,42,25,16SELECT usser,no,Type=attribute, Num=valueFROM

5、 tUNPIVOT(value FOR attribute IN(a, b, c) AS UPV這句話中， a,b,c 是列的名字，列轉行備注value FOR attribute IN(a, b, c)但是列名不能出如今上句的 select 語句中。- 結果/* usser no Type num111111121212a21b34c24a42b25c16 */T-SQL語句中，PIVOT命令可以實現數據表的列轉行，UNPIVOTW么與其相反，實現數據的行轉列。本文結合實例說明了這一過程，希望能對您有所幫助。AD： WOT2021 互聯網運維與開發(fā)者大會 熱銷搶票一、使用 PIVOT和UNP

6、IVO愉令的SQL Server版本要求1. 數據庫的最低版本要求為 SQL Server 2005 或更高。2. 必須將數據庫的兼容級別設置為 90 或更高。3. 查看我的數據庫版本及兼容級別。假設不知道怎么看數據庫版本或兼容級別的話可以在 SQL Server Management Studio新建一個查詢窗口輸入： print version ，運行之后在我的本機上得到：Apr 2 2021 15:53:02Copyright (c) Microsoft CorporationExpress Edition with Advanced Services on Windows NT 5.2

7、 (Build 3790:Service Pack 2)然后我們選擇一個數據庫然后右鍵-屬性 選擇選項得到以下圖的信息。在確認數據庫的版本和兼容級別符合1,2點的要求后你才可以接著繼續(xù)往下學習。二、使用PIVOT實現數據表的列轉行1.在這里我們先構建一個測試數據表（這里使用的是臨時表，以方便我們在退出會話的時候自動刪除表及其數據）首先我們先設計一個表架構為#Student 學生編號PK,姓名，性別，所屬班級的表，然后編寫如下T-SQL-創(chuàng)立臨時表（僅演示，表構造的不合理還請包涵）1. CREATE TABLE #Student (2.3. 學生編號INT IDENTITY。，1) PRIMAR

8、Y KEY,4.5. 姓名NVARCHAR(20),6.7. 性別NVARCHAR(1),8.9. 所屬班級NVARCHAR(20)給臨時表插入數據1. INSERT INTO #Student (2.3. 姓名, 性別, 所屬班級4.5. )7. SELECT李妹妹,女， 初一 1 班UNIONALL8.9. SELECT泰強, 10.11. SELECT泰映, 12.13. SELECT何謝, 14.15. SELECT李春, 16.17. SELECT吳歌, 18.19. SELECT林純, 20.21. SELECT徐葉, 22.23. SELECT龍門, 24.25. SELECT小

9、紅, 26.27. SELECT小李, 28.29. SELECT小黃, 30.31. SELECT旺財, 32.33. SELECT強強,男,初一 1 男,初一 1 男,初一 1 男,初二1 男,初二1 男,初二1 女,初二1 男,初三1 女,初三1 男,初三1 女,初三2 男,初三2 男,初二1班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班UNIONALL班；以下是查詢的結果:2泰強男初一 1 班3泰映男初一 1 班4何謝男初一 1 班5

10、李春男初二1 班6吳歌男初二1 班7林純男初二1 班8徐葉女初二1 班9龍門男初三1 班10小紅女初三1 班11小李男初三1 班12小黃女初三2 班13旺財男初三2 班14強強男初二1 班3. 所屬班級AS 班級, 4.5. COUNT(1) AS 人數6.7. FROM #Student8.9. GROUP BY 所屬班級 10.11. ORDER BY 人數DESC班級人 數初二15初一 14初三13初三22好了，在這里我希望把上面的表 班級，人數由 班級行的顯示轉換為 班級 列的顯示格式！在此你會看到第一個 PIVOT例如。是否很期待？?例如1. SELECT2.3. 班級總人數：AS

11、總人數,4.5. 初一 1班初一 2班,6.7. 初二 1 班,8.9. 初三1班初三2班10.12.13. SELECT14.15. 所屬班級AS 班級,16.17. 學生編號18.19. FROM #Student20.21. ) AS SourceTable22.23. PIVOT (24.25. COUNT(學生編號)26.27. FOR 班級IN (28.29. 初一 1班 初一 2班,30.31. 初二 1 班,32.33. 初三1班初三2班34.35. )36.37. ) AS PivotTable|總認數 |初一【班|初一彼|初二曲|初三彼|初三破1 1班輾總4擻4 C 5 P

12、 2clctU-IDR http力iMWJjcnbkjgurn/higiicccl/|在結果表中我們看到了對于不存在的班級初一2班它的總人數為0,這符合我們預期的結果！解釋：使用POVIT首先你需要在FROM?句內定義2個表：A. 一個稱為源表(SourceTable)。B.另一個稱為數據透視表(PivotTable)。語法：3. ,4.5. 第一個透視列 AS,6.7. 第二個透視列 AS,1. 最后一個透視列 AS 12.13. FROM (14.15. 16.17. ) AS18.19. PIVOT (20.21. ( )22.23. FOR IN (24.25. 第一個

13、透視列 , 第二個透視列 ,9. 最后一個透視列 30.31. )32.33. ) AS 34.35. ;以上的PIVOT子句內的第1n個透視列的值均為需要轉換為行的列的常量值，需要用口括起，支持GUID,字符串及各種數字！行轉列的應用例如- 使用 PIVOT 查詢班級內的男女學生人數及總人數1. SELECT2.3. 所屬班級 AS 班級 ,4.5. 男AS 男生人數,6.7. 女AS 女生人數,8.9. 男+ 女AS 總人數10.11. FROM （12.13. SELECT 學生編號,所屬班級, 性別FROM #Student14.15. ） AS SourceTab

14、le16.17. PIVOT （18.19. COUNT（學生編號）20.21. FOR 性別IN （22.23. 男女24.25. ）26.27. ） AS PivotTable28.29. ORDER BY 總人數DESC班級男生人數女生人數總人數1;初二1班4152._初一 1班314初三1班213_4_初三圈王112“刖網團 卜必口: rtMW七制鬼|匕一0出1寸七門W、使用UNPIVOT實現的功能其實與 PIVOT恰恰相反1. SELECT2.3. 未逆透視的列,4.5. 合并后的列AS 列別名,7. 行值的列名 AS 8.9. FROM (10.11. 12.13. ) AS 14

15、.15. UNPIVOT (16.17. 18.19. FOR IN (20.21. 第一個合并列 , 第二個合并列 ,5. 最后一個合并列 26.27. )28.29. ) AS 30.31. ;很浮云，不怕，這里帶例子(繼續(xù)使用II中用到的PIVOT表)- 源表1. SELECT2.3. 班級總人數 : AS 總人數 ,4.5. 初一 1 班, 初一 2 班 ,6.7. 初二1 班,8.9. 初三1 班, 初三 2 班 10.11. INTO #PivotTable - 為了使表達意圖更明晰, 我把 PIVOT 處理后的表放到一個臨時表當中12.14.15. SELEC

16、T16.17. 所屬班級AS 班級,18.19. 學生編號20.21. FROM #Student22.23. ) AS SourceTable24.25. PIVOT (26.27. COUNT(學生編號)28.29. FOR 班級IN (30.31. 初一 1班 初一 2班,32.33. 初二 1 班,34.35. 初三1班初三2班36.37. )38.39. ) AS PivotTablen ResJts _j Mssssges | a Execution pldr )星遞.i. J初一 1班|初一鬻f j初二1班|初三1班|初三國f |1 I：見魅總人數.17 p 532將多個列合并到

17、單個列的轉換的語句！!-結果1. SELECT2.3. 班級總人數4.5. FROM (6.7. SELECT9. 初一 1班初一 2班,10.11. 初二 1 班,12.13. 初三1班初三2班14.15. FROM16.17. #PivotTable18.19. ) AS s20.21. UNPIVOT (22.23. 總人數24.25. FOR 班級IN (26.27. 初一 1班初一 2班,28.29. 初二 1 班,30.31. 初三1班初三2班32.33. )34.35. ) AS un_p研皴 |總品冢| r I.J_fS-W 執(zhí)行下面代碼： SELECT 3.所屬班級AS 班級

18、, 5.男AS 男生人數,42_初一 M03初二1班5_4_初三1班35初三我2:ldtM磯CjR http/AL7 女 AS 女生人數 ,89 男 + 女 AS 總人數 1011 INTO #PivotTable2 - 放到臨時表方便查詢1213 FROM （1415 SELECT 學生編號 , 所屬班級 , 性別 FROM #Student1617 ） AS SourceTable1819 PIVOT （2021 COUNT（ 學生編號 ）2223 FOR 性別 IN （2425 男, 女2627 ）2829 ） AS PivotTable3031 ORDER BY 總人數 DESC3233 SELECT3435 班級 ,3637 男生或女生人數