Azure Derived Column - Create another column from the value of a column based on the value of another column

user_8869044 0
Viewed 326

I have a table something like:

Country 2018 2019 2020 LastKnownYear
Spain 1 2 3 2019
Germany 4 5 6 2018
Italy 7 8 9 2020

I want to add another column "latestKnownValue"

Country 2018 2019 2020 LastKnownYear latestKnownValue
Spain 1 2 3 2019 2
Germany 4 5 6 2018 4
Italy 7 8 9 2020 9

I've simplified the example, while I'm working with years, the reality of the table is that there are many years (all as columns) in the source table.

How can I do this using the "Derived Column" activity in Azure. Or is there another way of doing this in Azure?

azure-synapse azure-data-factory azure
2 Answers

An even much simpler example in ADF data flow derived column:

Create a new column called 'latestKnownValue' and use this expression:

toInteger(byName('LastKnownYear'))

user_73507880

A simple T-SQL CASE statement should suffice:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;

CREATE TABLE #tmp (
    Country         VARCHAR(20) PRIMARY KEY,
    [2018]          INT NOT NULL,
    [2019]          INT NOT NULL,
    [2020]          INT NOT NULL,
    LastKnownYear   INT NOT NULL
);

INSERT INTO #tmp
SELECT 'Spain', 1,  2,  3,  2019
UNION ALL
SELECT 'Germany',   4,  5,  6,  2018
UNION ALL
SELECT 'Italy', 7,  8,  9,  2020;


SELECT *,
    CASE LastKnownYear
        WHEN '2018' THEN [2018]
        WHEN '2019' THEN [2019]
        WHEN '2020' THEN [2020]
        ELSE NULL
    END AS latestKnownValue--,
    --CHOOSE ( LastKnownYear - 2017, [2018], [2019], [2020] ) AS chooseExample

FROM #tmp;
  

;WITH cte AS (
SELECT *
FROM #tmp
UNPIVOT ( xvalue for xyear In ( [2018], [2019], [2020] ) ) upvt
)
SELECT 
    t.Country, t.[2018], t.[2019], t.[2020], t.LastKnownYear, 
    c.xvalue AS lastKnownValue
FROM #tmp t
    INNER JOIN cte c ON t.Country = c.Country
        AND t.LastKnownYear = c.xyear;

You will not have access to CHOOSE in Azure Synapse Analytics dedicated SQL pools just now but it's really the same thing, syntactic sugar. Another alternative would be to UNPIVOT your columns then join as per my last example.

user_15275040
Related