SQL Server行列轉換學習日記-有解無憂

參考自https://www.cnblogs.com/wanghonghu/p/5631662.html

一，行轉列

創建行轉串列和插入資料

CREATE TABLE RowConvertToColumn(
    USERNAME VARCHAR(40)  NULL,
	COURSE VARCHAR(40)  NULL,
	SCORE NUMERIC(10,2)  NULL
) 

INSERT INTO RowConvertToColumn VALUES('張三','語文',84);
INSERT INTO RowConvertToColumn VALUES('張三','數學',94);
INSERT INTO RowConvertToColumn VALUES('張三','外語',76);
INSERT INTO RowConvertToColumn VALUES('李四','語文',88);
INSERT INTO RowConvertToColumn VALUES('李四','數學',95);
INSERT INTO RowConvertToColumn VALUES('李四','外語',80);
INSERT INTO RowConvertToColumn VALUES('王五','語文',98);
INSERT INTO RowConvertToColumn VALUES('王五','數學',96);
INSERT INTO RowConvertToColumn VALUES('王五','外語',70);
INSERT INTO RowConvertToColumn(USERNAME) VALUES('趙六');

查詢一遍資料

在這里插入圖片描述
1，case …when… 寫法

SELECT USERNAME 姓名 ,		
		MAX(CASE COURSE WHEN '語文' THEN SCORE ELSE 0 END ) AS 語文,
		MAX (CASE COURSE WHEN '數學' THEN SCORE ELSE 0 END ) AS 數學,
		MAX(CASE COURSE WHEN '外語' THEN  SCORE ELSE 0 END  ) AS 外語
FROM RowConvertToColumn GROUP BY USERNAME ORDER BY USERNAME ;

效果：

在這里插入圖片描述

2，PIVOT函式快速實作行轉列

 PIVOT函式的格式如下：

 PIVOT(<聚合函式>([聚合列值]) FOR [行轉列前的列名] IN([行轉列后的列名1],[行轉列后的列名2],[行轉列后的列名3],.......[行轉列后的列名N]))

實際操作：

SELECT USERNAME 姓名 , 語文, 數學,外語 FROM RowConvertToColumn PIVOT(MAX(SCORE) FOR COURSE IN (語文,數學,外語 ))AS T

效果：

在這里插入圖片描述

3，動態實作行轉列

declare @sql nvarchar(2000)
select @sql=ISNULL(@sql+',','')+course
from RowConvertToColumn
group by course
set @sql='select * from RowConvertToColumn pivot (max(score) for course in ('+@sql+')) a'
exec(@sql)

效果：

在這里插入圖片描述

二，列轉換行

創建列轉行表和插入資料

CREATE TABLE ColumnConvertToRow(
	[姓名] VARCHAR(40) NULL,
	[語文] NUMERIC(10,2)  NULL,
	[數學] NUMERIC(10,2)  NULL,
	[外語] NUMERIC(10,2)  NULL
) 

INSERT INTO ColumnConvertToRow VALUES('張三',85,78,64);
INSERT INTO ColumnConvertToRow VALUES('李四',89,54,78);
INSERT INTO ColumnConvertToRow VALUES('王五',64,78,97);

原始資料：

在這里插入圖片描述

1，靜態sql列轉行,當列頭較少時使用

SELECT * FROM 
(
   SELECT 姓名,課程='語文',分數=語文 FROM ColumnConvertToRow
   UNION ALL
   SELECT 姓名,課程='數學',分數=數學 FROM ColumnConvertToRow
   UNION ALL 
   SELECT 姓名,課程='外語',分數=外語 FROM ColumnConvertToRow
) T ORDER BY 姓名

效果：

在這里插入圖片描述

2，使用UNPIVOT函式，該陳述句必須sqlserver2005及以上版本才能使用

UNPIVOT函式的格式如下：

UNPIVOT([轉換為行的列值在轉換后對應的列名] for [轉換為行的列名在轉換后對應的列名] in ([轉換為行的列1],[轉換為行的列2],[轉換為行的列3],...[轉換為行的列N]))

實際操作：

SELECT 姓名,科目, 分數 FROM ColumnConvertToRow UNPIVOT(分數 FOR 科目 IN(語文,數學,外語)) AS T ORDER BY 姓名;

效果：

在這里插入圖片描述

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/195196.html

標籤：其他

上一篇：2020-10-27

下一篇：【Demo】采購訂單識訓Bapi

SQL Server行列轉換學習日記

一，行轉列

二 ，列轉換行

二，列轉換行