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

发布时间：2020-12-12 07:55:20 所属栏目：MsSql教程来源：网络整理

导读：感兴趣的小伙伴，下面一起跟随编程之家 jb51.cc的小编两巴掌来看看吧！代码如下: --行转列问题 --建立測試環境 Create Table TEST (DATES Varchar(6),EMPNO Varchar(5),STYPE Varchar(1),AMOUNT Int) --插入數據 Insert TEST Select 200605,02436,A,5 Union

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

代码如下:

 
--行转列问题 
--建立測試環境 
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','B',3 
Union All Select '200605','C','D',2 
Union All Select '200605','E',9 
Union All Select '200605','F',7 
Union All Select '200605','G',6 
Union All Select '200605','02438',8 
Union All Select '200605',0 
Union All Select '200605',4 
Union All Select '200605',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,[48] From Test
Union all
Select City,[50] From Test
Union all
Select City,[52] From Test

就可以了

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!