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

这篇文章主要为大家详细介绍了SQL Server 数据库表行转列，列转行终极方案，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随四海网的小编两巴掌来看看吧！

代码如下:

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

就可以了

本文来自：http://www.q1010.com/179/7480-0.html

注：关于SQL Server 数据库表行转列，列转行终极方案的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：SQL SERVER