行转列
select jcxm,
isnull([2020-01],0) [2020-01],
isnull([2020-02],0) [2020-02],
isnull([2020-03],0) [2020-03],
isnull([2020-04],0) [2020-04],
isnull([2020-05],0) [2020-05],
isnull([2020-06],0) [2020-06],
isnull([2020-07],0) [2020-07],
isnull([2020-08],0) [2020-08],
isnull([2020-09],0) [2020-09],
isnull([2020-10],0) [2020-10],
isnull([2020-11],0) [2020-11],
isnull([2020-12],0) [2020-12]
from(
select jcxm,ym,pjnd from V_ZTTJ_JCXM_MIC
)as u
pivot(max(pjnd) for ym in([2020-01],[2020-02],[2020-03],[2020-04],[2020-05],[2020-06],[2020-07],[2020-08],[2020-09],[2020-10],[2020-11],[2020-12])
)as e;
--注意:pivot后必须跟max或min等函数,不能直接写字段名
列转行:unpivot
SELECT FSX,TYPE,VAL FROM (
SELECT TYPE, VAL FROM (
SELECT FXY1,FXY2
from TABLE_A EVA
LEFT JOIN TABLE_B ENT ON EVA.SP_ID = ENT.id
LEFT JOIN TABLE_C JBXX ON JBXX.ID = ENT.enterId
WHERE EVA.delFlag = 0
AND EVA.SJZT = 2
) AS A UNPIVOT ( VAL FOR TYPE IN ( FXY1,FXY2
) ) AS B
) SAN
LEFT JOIN TABLE_D CONF ON CONF.ZDM = SAN.TYPE
ORDER BY CONF.[INDEX] ASC
列转行:pivot
SELECT * FROM (
SELECT ID ,LX FROM table_A
) AS src
PIVOT (
count(LX) FOR LX IN (A,B,C)
) AS piv