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

时间:2021-08-17 03:06:14

代码如下:


--行转列问题 
--建立測試環境 
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 

就可以了