Postgres Pro
Downloads
Home   >   mailing lists

Fwd: TSQL To Postgres - Unpivot/Union All - Mailing list pgsql-general

From Anthony Apollis
Subject Fwd: TSQL To Postgres - Unpivot/Union All
Date
Msg-id CAJyMCYLL-mMuCfXT8M1kD-=6=r8YHHrSGbT-rN25vCwtVyr1jg@mail.gmail.com
Whole thread Raw
Responses Re: TSQL To Postgres - Unpivot/Union All
Re: TSQL To Postgres - Unpivot/Union All
Re: Fwd: TSQL To Postgres - Unpivot/Union All
List pgsql-general
Tree view





I am trying to convert this TSQL:
INSERT INTO temp_FieldFlowsFact



SELECT [Account],[Calendar day],[Financial year],[Period]
,CASE
WHEN [Metric] like '%R12M%' THEN 'R12M'
WHEN [Metric] like '%R6M%' THEN 'R6M'
WHEN [Metric] like '%R3M%' THEN 'R3M'
ELSE 'Periodic'
END AS [Period type]
,[Metric],[Metric Value]

FROM
(
----Calculating Rolling FTRs
SELECT  [Account], [Calendar day] ,[Financial year],[Period]


,[Issue]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Issues]

,[Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers In]

,[Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers Out]

,[Return]
,SUM([Return]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Return]


,ROUND(ISNULL(([Transfers Out]+[Return])/NULLIF(([Issue]+[Transfers In]),0),0)*100,2) AS [FTR%]  --Calculating FTR%






,ROUND(ISNULL((   SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)  
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
)

/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)

),0),0)*100,2) AS [R12M FTR%]  --Calculating R12M FTR%



,ROUND(ISNULL((   SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)  
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)
)

/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING)

),0),0)*100,2) AS [R6M FTR%]  --Calculating R6M FTR%



,ROUND(ISNULL((   SUM([Transfers Out]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)  
+
SUM([Return]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)
)

/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)

),0),0)*100,2) AS [R3M FTR%]  ---Calculating R3M FTR%


FROM #temp_FlowsFact) AS FACT

--WHERE [Account]='SA11'
UNPIVOT  
(              [Metric Value] FOR [Metric] IN (
 [Issue],[R12M Issues],[R6M Issues],[R3M Issues],[Transfers In],[R12M Transfers In]
,[R6M Transfers In],[R3M Transfers In],[Transfers Out],[R12M Transfers Out],[R6M Transfers Out]
,[R3M Transfers Out],[Return],[R12M Return],[R6M Return],[R3M Return],[FTR%],[R12M FTR%],[R6M FTR%],[R3M FTR%]
)
)AS unpvt
--WHERE [Account] ='AE25' AND [Metric] like '%FTR%'
ORDER BY [Calendar day] 
To Postgres. The above code gives outputs for columns:
image.png
The Postgres i used: INSERT INTO temp_FieldFlowsFact
SELECT "Account", "Calendar day", "Financial year", "Period",
    CASE
        WHEN "Metric" LIKE '%R12M%' THEN 'R12M'
        WHEN "Metric" LIKE '%R6M%' THEN 'R6M'
        WHEN "Metric" LIKE '%R3M%' THEN 'R3M'
        ELSE 'Periodic'
    END AS "Period type",
    "Metric", "Metric Value"
FROM (
    -- Calculating Rolling FTRs
    SELECT "Account", "Calendar day", "Financial year", "Period",
        "Issue",
        SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Issues",
        SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Issues",
        SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Issues",
        "Transfers In",
        SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers In",
        SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers In",
        SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers In",
        "Transfers Out",
        SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers Out",
        SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers Out",
        SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers Out",
        "Return",
        SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Return",
        SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Return",
        SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Return",
        ROUND(COALESCE(("Transfers Out" + "Return") / NULLIF(("Issue" + "Transfers In"), 0), 0) * 100, 2) AS "FTR%",
        ROUND(COALESCE((
                SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
                SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
            ) /
            NULLIF((
                SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
                SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
            ), 0), 0) * 100, 2) AS "R12M FTR%",
        ROUND(COALESCE((
                SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
                SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
            ) /
            NULLIF((
                SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) +
                SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
            ), 0), 0) * 100, 2) AS "R6M FTR%",
        ROUND(COALESCE((
                SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
                SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
            ) /
            NULLIF((
                SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
                SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
            ), 0), 0) * 100, 2) AS "R3M FTR%"
    FROM temp_FlowsFact
) AS FACT
UNION ALL
SELECT "Account", "Calendar day", "Financial year", "Period",
    CASE
        WHEN "Metric" = 'Issue' THEN 'Issue'
        WHEN "Metric" = 'R12M Issues' THEN 'R12M Issues'
        WHEN "Metric" = 'R6M Issues' THEN 'R6M Issues'
        WHEN "Metric" = 'R3M Issues' THEN 'R3M Issues'
        WHEN "Metric" = 'Transfers In' THEN 'Transfers In'
        WHEN "Metric" = 'R12M Transfers In' THEN 'R12M Transfers In'
        WHEN "Metric" = 'R6M Transfers In' THEN 'R6M Transfers In'
        WHEN "Metric" = 'R3M Transfers In' THEN 'R3M Transfers In'
        WHEN "Metric" = 'Transfers Out' THEN 'Transfers Out'
        WHEN "Metric" = 'R12M Transfers Out' THEN 'R12M Transfers Out'
        WHEN "Metric" = 'R6M Transfers Out' THEN 'R6M Transfers Out'
        WHEN "Metric" = 'R3M Transfers Out' THEN 'R3M Transfers Out'
        WHEN "Metric" = 'Return' THEN 'Return'
        WHEN "Metric" = 'R12M Return' THEN 'R12M Return'
        WHEN "Metric" = 'R6M Return' THEN 'R6M Return'
        WHEN "Metric" = 'R3M Return' THEN 'R3M Return'
        WHEN "Metric" = 'FTR%' THEN 'FTR%'
        WHEN "Metric" = 'R12M FTR%' THEN 'R12M FTR%'
        WHEN "Metric" = 'R6M FTR%' THEN 'R6M FTR%'
        WHEN "Metric" = 'R3M FTR%' THEN 'R3M FTR%'
    END AS "Period type",
    "Metric Value", "Metric"
FROM temp_FlowsFact
WHERE "Metric" IN (
    'Issue', 'R12M Issues', 'R6M Issues', 'R3M Issues', 'Transfers In', 'R12M Transfers In',
    'R6M Transfers In', 'R3M Transfers In', 'Transfers Out', 'R12M Transfers Out', 'R6M Transfers Out',
    'R3M Transfers Out', 'Return', 'R12M Return', 'R6M Return', 'R3M Return', 'FTR%', 'R12M FTR%', 'R6M FTR%', 'R3M FTR%'
)
ORDER BY "Calendar day";
Does not give me outputs for columns: Metric and Pricing Channel:
image.png

What am i doing wrong? Please assist?
Attachment

pgsql-general by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: How to improve the performance of my SQL query？
Next
From: "David G. Johnston"
Date:
Subject: Re: TSQL To Postgres - Unpivot/Union All