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

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

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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

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