|
|
我需要转换redshift的SQL Server脚本。
2 y% Q( z; o" }% k2 q5 e8 N我改变了部分8 p5 d$ ?( q9 P9 v+ }
这是我遇到问题的脚本的一部分。
# O+ I Y" e: T4 V- _; i& e9 r, r5 ? SELECT a.*, b.*FROM (SELECT u.ContactId, u.Description, CONVERT(Float,u.SeatCharge) AS SeatCharge --CAST(u.SeatCharge AS numeric AS SeatCharge, --CONVERT(INT,CASE WHEN IsNumeric(CONVERT(VARCHAR(12),u.SeatCharge)) = 1 then CONVERT(VARCHAR(12),u.SeatCharge) else 0 End)FROM(SELECT md.contactid, CONVERT(float,MAX(CASE WHEN md.fieldid= 7172 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Core, CONVERT(float,MAX(CASE WHEN md.fieldid= 7182 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_RCM, CONVERT(float,MAX(CASE WHEN md.fieldid= 7192 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Advanced_RCM, CONVERT(float,MAX(CASE WHEN md.fieldid= 7183 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Payroll, CONVERT(float,MAX(CASE WHEN md.fieldid= 7184 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_DXM, CONVERT(float,MAX(CASE WHEN md.fieldid= 7185 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Messaging, CONVERT(float,MAX(CASE WHEN md.fieldid= 7186 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Tasks, CONVERT(float,MAX(CASE WHEN md.fieldid= 7173 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Core, CONVERT(float,MAX(CASE WHEN md.fieldid= 7187 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Data_Collection, CONVERT(float,MAX(CASE WHEN md.fieldid= 7189 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Auditing_Tools, CONVERT(float,MAX(CASE WHEN md.fieldid= 7190 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Group_Sessions, CONVERT(float,MAX(CASE WHEN md.fieldid= 7191 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_ABC_Data, CONVERT(float,MAX(CASE WHEN md.fieldid= 7211 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_AGA, CONVERT(float,MAX(CASE WHEN md.fieldid= 4495 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Core, CONVERT(float,MAX(CASE WHEN md.fieldid= 7175 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_PTO, CONVERT(float,MAX(CASE WHEN md.fieldid= 8106 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Course_Groups, CONVERT(float,MAX(CASE WHEN md.fieldid= 8286 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Reach_Me, CONVERT(float,MAX(CASE WHEN md.fieldid= 8999 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Redshift, CONVERT(float,MAX(CASE WHEN md.fieldid= 9155 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Benefits, CONVERT(float,MAX(CASE WHEN md.fieldid= 9156 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Assets FROM public.contact_meta md WHERE md.fieldid IN (4495、7172、7192、7192、7183、7184、7185、7186、7183、7187、7189、7199、7190、7191、7175、721、7212、7192、7192、7191、7191、7191、719、7195、7195、7195、7195、7195、7195、7195--AND md.ContactId = GROUP BY md.contactid s--UNPIVOT--(--SeatCharge--FOR Description IN ( -- PM_Core, -- PM_RCM, -- PM_Advanced_RCM, -- PM_Payroll, -- PM_DXM, -- PM_Messaging, -- PM_Tasks, --HRIS_Assets)--)uWHERE u.SeatCharge a一切都在工作，而不是UNPIVOT功能。
6 ]8 n7 Y( {2 G: n& z- D4 d据我所知，没有红移UNPIVOT功能。% F" L7 T' _: j
所以现在需要正确重写吗？: S2 D; Q7 s- v! ~9 d% v
感谢帮助。
# f5 c* B V" l3 q ) |5 @6 f' w+ P
解决方案: |
|
技术问答
207 人阅读
|
0 人回复
|
2023-09-12
