SQL Server 資料庫中行變列的SQL語句寫法

SQL Server 資料庫中行變列的SQL語句寫法:

求面積的方法:

select LZND,
 sum(case when lzfs='出租' then isnull(JFLZMJM,0)+isnull(YFLZMJM,0) else 0 end) as mjm_cz,
 sum(case when lzfs='入股' then isnull(JFLZMJM,0)+isnull(YFLZMJM,0) else 0 end) as mjm_rg,
 sum(case when lzfs='轉包' then isnull(JFLZMJM,0)+isnull(YFLZMJM,0) else 0 end) as mjm_zb,
 sum(case when lzfs='轉讓' then isnull(JFLZMJM,0)+isnull(YFLZMJM,0) else 0 end) as mjm_zr,
 sum(case when lzfs='互換' then isnull(JFLZMJM,0)+isnull(YFLZMJM,0) else 0 end) as mjm_hh,
 sum(case when lzfs='其他' then isnull(JFLZMJM,0)+isnull(YFLZMJM,0) else 0 end) as mjm_qt
 from lz_ht_base 
 group by LZND

求個數的方法:

select LZND,
sum(case when lzfs='出租' then 1 else 0 end) as gs_cz,
sum(case when lzfs='入股' then 1 else 0 end) as gs_rg,
sum(case when lzfs='轉包' then 1 else 0 end) as gs_zb,
sum(case when lzfs='轉讓' then 1 else 0 end) as gs_zr,
sum(case when lzfs='互換' then 1 else 0 end) as gs_hh,
sum(case when lzfs='其他' then 1 else 0 end) as gs_qt
from lz_ht_base 
group by LZND

select LZND,lzfs from  lz_ht_base

----the---end----