复制代码 代码如下: --行转列问题 --建立測試環境 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', '0
复制代码 代码如下: --行转列问题 --建立測試環境 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 就可以了
