[SQL] 資料列運算

在論壇上看見的問題 - 不良率呈現，同一個資料來源有兩種需求，筆記如下

資料來源

use tempdb
go

DROP TABLE IF EXISTS tblQ

CREATE TABLE tblQ (庫別 char(1) , 日期 char(2) , 類別 nvarchar(10) , 數量 int)
INSERT INTO tblQ (庫別 , 日期 , 類別 , 數量)
SELECT 'A' , '01' , N'螺絲-次' ,2 
UNION ALL  		  
SELECT 'A' , '01' , N'螺絲-全' ,1000 
UNION ALL  		  
SELECT 'B' , '01' , N'螺絲-次' ,3 
UNION ALL  		  
SELECT 'B' , '01' , N'螺絲-全' ,1100
UNION ALL  		  
-- 以下為自行加入資料
SELECT 'C' , '02' , N'螺絲-次' , 999
UNION ALL  		  
SELECT 'C' , '02' , N'螺絲-全' , 0

Q1：資料列運算

要把資料列進行百分比運算後並產生一筆百分比資料

SELECT *
FROM
	(
		SELECT 庫別 , 日期 , 類別 , 數量
		FROM tblQ
		UNION ALL
		SELECT 庫別 , 日期 , N'螺絲-率' , [螺絲-次] * 1.0 / NULLIF([螺絲-全] , 0) * 1.0
		FROM tblQ AS P
			PIVOT
				(
					SUM(數量) FOR 類別 IN ([螺絲-次] , [螺絲-全])
				) AS PV
	) AS F
ORDER BY 
	庫別 , 
	日期 , 
	CASE 類別 
		WHEN N'螺絲-次' THEN 1
		WHEN N'螺絲-全' THEN 2
		WHEN N'螺絲-率' THEN 3
		ELSE 4
	END , 
	數量
  

Q2：資料轉置

把 A1 百分比資料進行轉置

;
WITH CTE AS
(
	SELECT 庫別 , 日期 , 類別 , 數量
	FROM tblQ
	UNION ALL
	SELECT 庫別 , 日期 , N'螺絲-率' , [螺絲-次] * 1.0 / NULLIF([螺絲-全] , 0) * 1.0
	FROM tblQ AS P
		PIVOT
			(
				SUM(數量) FOR 類別 IN ([螺絲-次] , [螺絲-全])
			) AS PV
)
SELECT PV.* 
FROM CTE AS P
	PIVOT
	(
		SUM(數量) FOR 日期 IN ([01] , [02])
	) AS PV
ORDER BY 
	庫別 , 
	CASE 類別 
		WHEN N'螺絲-次' THEN 1
		WHEN N'螺絲-全' THEN 2
		WHEN N'螺絲-率' THEN 3
		ELSE 4
	END