SQLServer行转列、列转行（SQL语句）

君不见#

已于 2023-10-24 13:33:31 修改

阅读量1k

点赞数

分类专栏： SQLServer 文章标签：数据库 sqlserver sql

于 2021-08-03 09:10:49 首次发布

本文链接：https://blog.youkuaiyun.com/UD_World/article/details/119342377

版权

SQLServer 专栏收录该内容

7 篇文章

订阅专栏

行转列

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