C# MVC SQLSERVER 矩陣轉置 - 靜態的行轉列

SQL 行轉列-靜態的行轉列

當在處理得到的集保戶庫存資料時，需要將資料Level (籌碼級距)做分類時就需要進行行轉列，如下圖原始資料

要將1 ~ 15 Level 的資料分類並且依照時間做條件，SQL如下
/****** SSMS 中 SelectTopNRows 命令的指令碼  ******/

SELECT [Year],[Month],[Day],
    SUM(CASE [Level] WHEN N'1' THEN [CHEP] ELSE 0 END) "Level1",
    SUM(CASE [Level] WHEN N'2' THEN [CHEP] ELSE 0 END) "Level2",
    SUM(CASE [Level] WHEN N'3' THEN [CHEP] ELSE 0 END) "Level3",
    SUM(CASE [Level] WHEN N'4' THEN [CHEP] ELSE 0 END) "Level4",
SUM(CASE [Level] WHEN N'5' THEN [CHEP] ELSE 0 END) "Level5",
    SUM(CASE [Level] WHEN N'6' THEN [CHEP] ELSE 0 END) "Level6",
    SUM(CASE [Level] WHEN N'7' THEN [CHEP] ELSE 0 END) "Level7",
    SUM(CASE [Level] WHEN N'8' THEN [CHEP] ELSE 0 END) "Level8",
SUM(CASE [Level] WHEN N'9' THEN [CHEP] ELSE 0 END) "Level9",
    SUM(CASE [Level] WHEN N'10' THEN [CHEP] ELSE 0 END) "Level10",
    SUM(CASE [Level] WHEN N'11' THEN [CHEP] ELSE 0 END) "Level11",
    SUM(CASE [Level] WHEN N'12' THEN [CHEP] ELSE 0 END) "Level12",
SUM(CASE [Level] WHEN N'13' THEN [CHEP] ELSE 0 END) "Level3",
    SUM(CASE [Level] WHEN N'14' THEN [CHEP] ELSE 0 END) "Level4",
    SUM(CASE [Level] WHEN N'15' THEN [CHEP] ELSE 0 END) "Level5"
FROM   [BochenLinTest].[dbo].[TDDC_9938]
Group By [Year],[Month],[Day]
order by Year,Month,day desc


結果圖片如下：

資料將會依照年月日，由小排到大，並且級距的資料變成Level 1~ 15