Rewrite code with UNPIVOT from sql to redshift

Question

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.

Parfait · Accepted Answer · 2018-09-12T17:35:06.993

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.

Hm. I get this error `[42601][500310] [Amazon](500310) Invalid operation: syntax error at or near "'PM_Core'" Position: 108;` — Eugene Sukh, Sep 11 '18 at 15:43
@EugeneSukh [Unsupported PostgreSQL Features](https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html): *Array constructors* - that's one of the reasons Redshift questions should not be tagged with postgresql — , Sep 11 '18 at 18:15
@EugeneSukh ... an ANSI SQL alternative is many `UNION` statements for each column. — Parfait, Sep 11 '18 at 18:27
Okay. so how I need to rewrite this part, to make it work in Redashift? @a_horse_with_no_name — Eugene Sukh, Sep 12 '18 at 10:48
Simply avoid the conditional aggregation that pivoted in the first place requiring your unpivot process. Just aggregate with a conditional *Description* column from a long `CASE` statement. See edit with note. — Parfait, Sep 12 '18 at 17:36

Rewrite code with UNPIVOT from sql to redshift

1 Answers1

Linked