探讨SQLSERVER 行列转换 其实就是它的执行顺序

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

image

 

行业转换的代码是:

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

image

 

再分组取值:

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

 

image

您可以选择一种方式赞助本站