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）

如果是列转行的话直接Union All就可以了

例如：

city style color 46 48 50 52
长沙 S6MF01002 152 1 2 2 1
长沙 S6MF01002 201 1 2 2 1
上面到下面的样子
city style color size qty
长沙 S6MF01002 152 46 1
长沙 S6MF01002 152 48 2
长沙 S6MF01002 152 50 2
长沙 S6MF01002 152 52 1
长沙 S6MF01002 201 46 1
长沙 S6MF01002 201 48 2
长沙 S6MF01002 201 50 2
长沙 S6MF01002 201 52 1

Select City,Style,Color,[46] From Test
Union all
Select City,Style,Color,[48] From Test
Union all
Select City,Style,Color,[50] From Test
Union all
Select City,Style,Color,[52] From Test

就可以了