mssql 数据库表行转列，列转行终极方案

--行转列问题
--建立測試環境
Create Table TEST
(DATES Varchar(6),
EMPNO Varchar(5),
STYPE Varchar(1),
AMOUNT Int)
--插入數據
Insert TEST Select 200605, 02436, A, 5
Union All Select 200605, 02436, B, 3
Union All Select 200605, 02436, C, 3
Union All Select 200605, 02436, D, 2
Union All Select 200605, 02436, E, 9
Union All Select 200605, 02436, F, 7
Union All Select 200605, 02436, G, 6
Union All Select 200605, 02438, A, 7
Union All Select 200605, 02438, B, 8
Union All Select 200605, 02438, C, 0
Union All Select 200605, 02438, D, 3
Union All Select 200605, 02438, E, 4
Union All Select 200605, 02438, F, 5
Union All Select 200605, 02438, G, 1
GO
--測試
--如果STYPE固定，可以這麼寫
Select
DATES,
EMPNO,
SUM(Case STYPE When A Then AMOUNT Else 0 End) As A,
SUM(Case STYPE When B Then AMOUNT Else 0 End) As B,
SUM(Case STYPE When C Then AMOUNT Else 0 End) As C,
SUM(Case STYPE When D Then AMOUNT Else 0 End) As D,
SUM(Case STYPE When E Then AMOUNT Else 0 End) As E,
SUM(Case STYPE When F Then AMOUNT Else 0 End) As F,
SUM(Case STYPE When G Then AMOUNT Else 0 End) As G
From TEST
Group By DATES,EMPNO
Order By DATES,EMPNO

--如果STYPE不固定，用動態語句
Declare @S Varchar(1000)
Set @S=
Select @S=@S+,SUM(Case STYPE When +STYPE+ Then AMOUNT Else 0 End) As +STYPE From (Select Distinct STYPE From TEST) A Order By STYPE
Set @S=Select DATES,EMPNO+@S+ From TEST Group By DATES,EMPNO Order By DATES,EMPNO
EXEC(@S)
GO
--如果被转置的是数字类型的话，应用下列语句
DECLARE @S VARCHAR(1000)
SET @S=SELECT DATES,EMPNO
SELECT @S=@S+,[+STYPE+]=SUM(CASE WHEN STYPE=+STYPE+ THEN AMOUNT ELSE 0 END)
FROM (Select Distinct STYPE From TEST) A Order By STYPE
SET @S=@S+ FROM TEST GROUP BY DATES,EMPNO
EXEC(@S)