考虑一下这种UNNEST(ARRAY(...))方法，该方法是@Stew从RedShift最初派生的Postgresql方言中借用的。具体来说，首先调用UNNEST列的字符串文字名称数组，然后再次调用实际列的数组，以在单独的行中返回指标和值。
SELECT s.ContactId,
UNNEST(ARRAY('PM_Core', 'PM_RCM', 'PM_Advanced_RCM', 'PM_Payroll', 'PM_DXM',
'PM_Messaging', 'PM_Tasks', 'Clinical_Core', 'Clinical_Data_Collection',
'Clinical_Auditing_Tools', 'Clinical_Group_Sessions', 'Clinical_Group_Sessions',
'Clinical_ABC_Data', 'Clinical_AGA', 'LMS_Core', 'HRIS_PTO', 'LMS_Course_Groups',
'PM_Reach_Me', 'PM_Redshift', 'HRIS_Benefits', 'HRIS_Assets')) AS Description,
UNNEST(ARRAY(PM_Core, PM_RCM, PM_Advanced_RCM, PM_Payroll, PM_DXM,
PM_Messaging, PM_Tasks, Clinical_Core, Clinical_Data_Collection,
Clinical_Auditing_Tools, Clinical_Group_Sessions, Clinical_Group_Sessions,
Clinical_ABC_Data, Clinical_AGA, LMS_Core, HRIS_PTO, LMS_Course_Groups,
PM_Reach_Me, PM_Redshift, HRIS_Benefits, HRIS_Assets)) AS SeatCharge
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,7182,7192,7183,7184,7185,7186,7173,7187,7189,7190,7191,7175,7211,7212,8106,8286,8999,9155,9156)
--AND md.ContactId = 75337
GROUP BY
md.contactid
) s
但是，请重新考虑枢轴，然后再取消枢轴。只需有条件地创建指标列CASE，然后进行汇总：
SELECT sub.contactid, sub.Description, MAX(sub."VALUE") As SeatCharge
FROM
(SELECT md.contactid,
CASE md.fieldid
WHEN 7172 THEN 'PM_Core'
WHEN 7182 THEN 'PM_RCM'
WHEN 7192 THEN 'PM_Advanced_RCM'
WHEN 7183 THEN 'PM_Payroll'
WHEN 7184 THEN 'PM_DXM'
WHEN 7185 THEN 'PM_Messaging'
WHEN 7186 THEN 'PM_Tasks'
WHEN 7173 THEN 'Clinical_Core'
WHEN 7187 THEN 'Clinical_Data_Collection'
WHEN 7189 THEN 'Clinical_Auditing_Tools'
WHEN 7190 THEN 'Clinical_Group_Sessions'
WHEN 7191 THEN 'Clinical_ABC_Data'
WHEN 7211 THEN 'Clinical_AGA'
WHEN 4495 THEN 'LMS_Core'
WHEN 7175 THEN 'HRIS_PTO'
WHEN 8106 THEN 'LMS_Course_Groups'
WHEN 8286 THEN 'PM_Reach_Me'
WHEN 8999 THEN 'PM_Redshift'
WHEN 9155 THEN 'HRIS_Benefits'
WHEN 9156 THEN 'HRIS_Assets'
ELSE NULL
END AS Description,
CONVERT(float, Value) AS VALUE
FROM public.contact_Meta md
WHERE md.fieldid IN (4495, 7172, 7182, 7192, 7183, 7184,7185, 7186, 7173, 7187, 7189,
7190, 7191, 7175, 7211, 7212,8106, 8286, 8999, 9155, 9156)
--AND md.ContactId = 75337
) AS sub
GROUP BY sub.contactid, sub.Description
注意：将来，请确保合并 描述 值的查找表，您可以将这些ID加入这些表中，并避免冗长的CASE逻辑计算甚至是longIN子句。
602392714
清零编程群