复制代码 代码如下:

--行转列问题

--建立測試環境

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

就可以了

【mssql 数据库表行转列，列转行终极方案】相关文章：

★ SQL Server 2005恢复数据库详细图文教程

★ sqlserver数据库迁移的几种方式

★ 跪求sql server2012行转列方案

★ 根据sql脚本修改数据库表结构

★ sql 批量修改数据库表

★ sql server 2005数据库备份还原图文教程

★ SQL2005Express中导入ACCESS数据库的两种方法

★ 恢复.mdf 数据库步骤

★ SqlServer 2005/2008数据库被标记为“可疑”的解决办法

★ sql2008 还原数据库解决方案