国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁 > 數據庫 > SQL Server > 正文

mssql 數據庫表行轉列,列轉行終極方案

2024-08-31 00:58:56
字體:
來源:轉載
供稿:網友

復制代碼 代碼如下:


--行轉列問題
--建立測試環境
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

就可以了
發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
主站蜘蛛池模板: 鄂伦春自治旗| 晋城| 阳原县| 孟州市| 漠河县| 钦州市| 宝丰县| 新闻| 新晃| 扎鲁特旗| 安龙县| 柘城县| 青田县| 濮阳县| 娄烦县| 临湘市| 安仁县| 平山县| 宁强县| 金华市| 高碑店市| 蒲江县| 德惠市| 铜鼓县| 宁陕县| 甘谷县| 灵石县| 临汾市| 宣武区| 会理县| 福贡县| 新蔡县| 佳木斯市| 徐州市| 南丰县| 永昌县| 深水埗区| 土默特左旗| 彰化县| 沛县| 汽车|