CODEWITHSUNDEEP
sqlamazon-redshiftunpivot
Eugene Sukh
Eugene Sukh

Reputation: 2727

Rewrite code with UNPIVOT from sql to redshift

I have SQL server script that needs to be converted to redshift.

I converted part of it

Here is part of the script where I have a problem with.

    SELECT  a.*,
        b.*

FROM    (

SELECT  u.ContactId,
        u.Description,
        CONVERT(Float,u.SeatCharge) AS SeatCharge
        --CAST(u.SeatCharge AS numeric (18,4)) 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,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

--UNPIVOT
--(
--SeatCharge
--FOR Description IN (
  --      PM_Core,
    --    PM_RCM,
      --  PM_Advanced_RCM,
       -- PM_Payroll,
       -- PM_DXM,
       -- PM_Messaging,
       -- PM_Tasks,

        --HRIS_Assets)
--)u



WHERE           u.SeatCharge <> ''

        )a

All is working, instead of UNPIVOT function.

As I know redshift doesn't have UNPIVOT function.

So now I need to rewrite it correctly?

Thank's for help.

Upvotes: 1

Views: 2848

Answers (1)

Parfait
Parfait

Reputation: 107687

Consider the UNNEST(ARRAY(...)) approach, borrowed from this approach by @Stew from the PostgreSQL dialect which RedShift originally derives. Specifically, first call UNNEST on array of string literal names of columns and then again on array of actual columns to return indicator and value in separate rows.

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

Random Data Demo

However, reconsider pivoting and then unpivoting. Simply create your metric column conditionally with CASE and then aggregate:

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

NOTE: In future, be sure to incorporate a lookup table of Description values that you can join these ids to and avoid the long CASE logic calculation and even long IN clause.

Upvotes: 1

Related Questions