探討SQLSERVER 行列轉換 其實就是它的執行順序

select top 10 rq,lb,zsz,ltsz from HQ_ZQGM_SCGM  order by  rq

 

行業轉換的代碼是：

select rq,MAX(case lb when 1 then zsz end) as zsz1,max(case lb when 2 then zsz end) as zsz2
,max(case lb when 0 then zsz end) as zsz3
,max(case lb when 1 then ltsz end) as ltsz1,max(case lb when 2 then ltsz end) as ltsz2
,max(case lb when 0 then ltsz end) as ltsz3
from HQ_ZQGM_SCGM group by rq ORDER BY RQ

以前的代碼不好理解 

分解的步驟： 

SELECT
select rq,(case lb when 1 then zsz end) as zsz1,(case lb when 2 then zsz end) as zsz2
,(case lb when 0 then zsz end) as zsz3
,(case lb when 1 then ltsz end) as ltsz1,(case lb when 2 then ltsz end) as ltsz2
,(case lb when 0 then ltsz end) as ltsz3
from HQ_ZQGM_SCGM

 

再分組取值：

select rq,MAX(case lb when 1 then zsz end) as zsz1,max(case lb when 2 then zsz end) as zsz2
,max(case lb when 0 then zsz end) as zsz3
,max(case lb when 1 then ltsz end) as ltsz1,max(case lb when 2 then ltsz end) as ltsz2
,max(case lb when 0 then ltsz end) as ltsz3
from HQ_ZQGM_SCGM group by rq ORDER BY RQ