Oracle 列传行UNPIVOT

语法:
UNPIVOT(新列名 FOR 聚合列名 IN (对应的列名1…列名n ))
INCLUDE | EXCLUDE NULLS 子句参数可以控制在结果集中是否保留值为NULL的行,默认为EXCLUDE NULLS,即去除空值行。
--列转换行UNPIVOT
SELECT * FROM 
(SELECT A.*,B.DEPT,B.SECTION,B.LZ,B.LZ_DATE 
FROM GC_DWT_OA.IMP_EMP_SCHEDULE_LIST  A
RIGHT JOIN  GC_DWT_OA.IMP_EMPLOYEE_DEPT B ON A.USER_ID=B.USER_ID
WHERE 1 = 1
AND A.USER_ID IN ('A0002719','A0002277','A0002245','A0002218') 
AND B.DEPT='MFG'
AND ATTENDANCE_PERIOD='202310'
)A
UNPIVOT INCLUDE NULLS (Value FOR Attribute IN 
(DATE26,DATE27,DATE28,DATE29,DATE30,DATE31,DATE01,DATE02,DATE03,DATE04,DATE05,DATE06,DATE07,DATE08,DATE09,DATE10,DATE11,DATE12,
DATE13,DATE14,DATE15,DATE16,DATE17,DATE18,DATE19,DATE20,DATE21,DATE22,DATE23,DATE24,DATE25))

 

posted @ 2023-11-02 14:16  bellin124  阅读(69)  评论(0编辑  收藏  举报