mapping back

  • i have table as below:
    country code
    australia 55555
    australia 55555
    australia checklater
    ukraine 33333
    ukraine checklater
    ukraine checklater
    ukraine 33333
    america 22222

    i would like to map back the same country and replace the code from checklater to mapping code. How can i do that.

    Expected result:
    country code
    australia 55555
    australia 55555
    australia 55555
    ukraine 33333
    ukraine 33333
    ukraine 33333
    ukraine 33333
    america 22222

  • girl_bj - Thursday, February 14, 2019 8:23 PM

    i have table as below:
    country code
    australia 55555
    australia 55555
    australia checklater
    ukraine 33333
    ukraine checklater
    ukraine checklater
    ukraine 33333
    america 22222

    i would like to map back the same country and replace the code from checklater to mapping code. How can i do that.

    Expected result:
    country code
    australia 55555
    australia 55555
    australia 55555
    ukraine 33333
    ukraine 33333
    ukraine 33333
    ukraine 33333
    america 22222

    First, you would have to know for certain that there are only two distinct values for any given country.. the checklater value, and the mapping code.  On that assumption, try this:
    CREATE TABLE #COUNTRY_CODES (
        country varchar(10),
        code varchar(10)
    );

    INSERT INTO #COUNTRY_CODES (country, code)
    VALUES    ('australia', '55555'),
            ('australia', '55555'),
            ('australia', 'checklater'),
            ('ukraine', '33333'),
            ('ukraine', 'checklater'),
            ('ukraine', 'checklater'),
            ('ukraine', '33333'),
            ('america', '22222');

    SELECT *
    FROM #COUNTRY_CODES;

    WITH CODE_VALUES AS (

        SELECT DISTINCT country, code
        FROM #COUNTRY_CODES
        WHERE code <> 'checklater'
    )
    UPDATE CC
    SET CC.code = CV.code
    FROM #COUNTRY_CODES AS CC
    INNER JOIN CODE_VALUES AS CV
        ON CC.country = CV.country;

    SELECT *
    FROM #COUNTRY_CODES;

    DROP TABLE #COUNTRY_CODES;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Friday, February 15, 2019 8:30 AM

    First, you would have to know for certain that there are only two distinct values for any given country.. the checklater value, and the mapping code.  On that assumption, try this:
    CREATE TABLE #COUNTRY_CODES (
        country varchar(10),
        code varchar(10)
    );

    INSERT INTO #COUNTRY_CODES (country, code)
    VALUES    ('australia', '55555'),
            ('australia', '55555'),
            ('australia', 'checklater'),
            ('ukraine', '33333'),
            ('ukraine', 'checklater'),
            ('ukraine', 'checklater'),
            ('ukraine', '33333'),
            ('america', '22222');

    SELECT *
    FROM #COUNTRY_CODES;

    WITH CODE_VALUES AS (

        SELECT DISTINCT country, code
        FROM #COUNTRY_CODES
        WHERE code <> 'checklater'
    )
    UPDATE CC
    SET CC.code = CV.code
    FROM #COUNTRY_CODES AS CC
    INNER JOIN CODE_VALUES AS CV
        ON CC.country = CV.country;

    SELECT *
    FROM #COUNTRY_CODES;

    DROP TABLE #COUNTRY_CODES;

    This runs faster on my test runs, because it only requires one scan of the table and one sort.
    WITH CODE_VALUES AS
    (
        SELECT cc.country, cc.code, MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) AS countrycode
        FROM #COUNTRY_CODES CC
    )
    UPDATE CV
    SET cv.code = cv.countrycode
    FROM CODE_VALUES AS CV
    WHERE cv.code = 'checklater';

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Friday, February 15, 2019 9:02 AM

    This runs faster on my test runs, because it only requires one scan of the table and one sort.
    WITH CODE_VALUES AS
    (
        SELECT cc.country, cc.code, MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) AS countrycode
        FROM #COUNTRY_CODES CC
    )
    UPDATE CV
    SET cv.code = cv.countrycode
    FROM CODE_VALUES AS CV
    WHERE cv.code = 'checklater';

    Drew

    CREATE TABLE #COUNTRY_CODES (
      country varchar(10),
      code varchar(10)
    );

    INSERT INTO #COUNTRY_CODES (country, code)
    VALUES  ('australia', '55555'),
       ('australia', '55555'),
       ('australia', 'checklater'),
       ('ukraine', '33333'),
       ('ukraine', 'checklater'),
       ('ukraine', 'checklater'),
       ('ukraine', '33333'),
       ('america', '22222'),

            -----------new condition-------------
            ('nigeria', 'checklater'),
            ('nigeria', 'checklater'),

            ('korea', '66666'),
            ('korea', '66666'),
            ('korea', 'checklater'),
            ('korea', '22222')

    SELECT *
    FROM #COUNTRY_CODES;

    i have a new condition, is it possible to apply in one query. I am doing an update on every each condition looks messy.

    Above new condition, if found same country with only checklater, replace to 'NEW' (sample on country=nigeria).
    if found county with checklater, and more than one different code, replace to the one which has more than one code (sample on country=korea)

    Expected result:
    country code
    australia 55555
    australia 55555
    australia 55555
    ukraine 33333
    ukraine 33333
    ukraine 33333
    ukraine 33333
    america 22222
    nigeria NEW
    nigeria NEW
    korea 66666
    korea 66666
    korea 66666
    korea 66666

  • girl_bj - Sunday, February 24, 2019 8:14 PM

    CREATE TABLE #COUNTRY_CODES (
      country varchar(10),
      code varchar(10)
    );

    INSERT INTO #COUNTRY_CODES (country, code)
    VALUES  ('australia', '55555'),
       ('australia', '55555'),
       ('australia', 'checklater'),
       ('ukraine', '33333'),
       ('ukraine', 'checklater'),
       ('ukraine', 'checklater'),
       ('ukraine', '33333'),
       ('america', '22222'),

            -----------new condition-------------
            ('nigeria', 'checklater'),
            ('nigeria', 'checklater'),

            ('korea', '66666'),
            ('korea', '66666'),
            ('korea', 'checklater'),
            ('korea', '22222')

    SELECT *
    FROM #COUNTRY_CODES;

    i have a new condition, is it possible to apply in one query. I am doing an update on every each condition looks messy.

    Above new condition, if found same country with only checklater, replace to 'NEW' (sample on country=nigeria).
    if found county with checklater, and more than one different code, replace to the one which has more than one code (sample on country=korea)

    Expected result:
    country code
    australia 55555
    australia 55555
    australia 55555
    ukraine 33333
    ukraine 33333
    ukraine 33333
    ukraine 33333
    america 22222
    nigeria NEW
    nigeria NEW
    korea 66666
    korea 66666
    korea 66666
    korea 66666

    The first one is a simple change to the query that I provided.  If you understand what the code is doing, you should be able to predict what the result for Nigeria will be, and thus how to produce your desired results.  Try it for yourself, and post your changes here.

    The second one isn't fully defined.  Specifically, what do you want to do in the case where there are multiple codes for a country and the all appear on more than one record?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Monday, February 25, 2019 12:24 PM

    The first one is a simple change to the query that I provided.  If you understand what the code is doing, you should be able to predict what the result for Nigeria will be, and thus how to produce your desired results.  Try it for yourself, and post your changes here.

    The second one isn't fully defined.  Specifically, what do you want to do in the case where there are multiple codes for a country and the all appear on more than one record?

    Drew

    I did something like this for first one.

    WITH CODE_VALUES AS
    (
      SELECT cc.country, cc.code
        ,case when MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) is null then 'NEW'
         else MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) end AS countrycode
      FROM #COUNTRY_CODES CC
    )
    UPDATE CV
    SET cv.code = cv.countrycode
    FROM CODE_VALUES AS CV
    WHERE cv.code = 'checklater';

    for the second condition im not sure how to apply it

    ('korea', '66666'),
    ('korea', '66666'),
    ('korea', 'checklater'),
    ('korea', '22222')

    if multiple code found 66666 then apply all as 66666, if multiple code found 22222 then all apply to 22222.

  • girl_bj - Monday, February 25, 2019 6:44 PM

    drew.allen - Monday, February 25, 2019 12:24 PM

    girl_bj - Sunday, February 24, 2019 8:14 PM

    drew.allen - Friday, February 15, 2019 9:02 AM

    sgmunson - Friday, February 15, 2019 8:30 AM

    girl_bj - Thursday, February 14, 2019 8:23 PM

    i have table as below:
    country code
    australia 55555
    australia 55555
    australia checklater
    ukraine 33333
    ukraine checklater
    ukraine checklater
    ukraine 33333
    america 22222

    i would like to map back the same country and replace the code from checklater to mapping code. How can i do that.

    Expected result:
    country code
    australia 55555
    australia 55555
    australia 55555
    ukraine 33333
    ukraine 33333
    ukraine 33333
    ukraine 33333
    america 22222

    First, you would have to know for certain that there are only two distinct values for any given country.. the checklater value, and the mapping code.  On that assumption, try this:
    CREATE TABLE #COUNTRY_CODES (
        country varchar(10),
        code varchar(10)
    );

    INSERT INTO #COUNTRY_CODES (country, code)
    VALUES    ('australia', '55555'),
            ('australia', '55555'),
            ('australia', 'checklater'),
            ('ukraine', '33333'),
            ('ukraine', 'checklater'),
            ('ukraine', 'checklater'),
            ('ukraine', '33333'),
            ('america', '22222');

    SELECT *
    FROM #COUNTRY_CODES;

    WITH CODE_VALUES AS (

        SELECT DISTINCT country, code
        FROM #COUNTRY_CODES
        WHERE code <> 'checklater'
    )
    UPDATE CC
    SET CC.code = CV.code
    FROM #COUNTRY_CODES AS CC
    INNER JOIN CODE_VALUES AS CV
        ON CC.country = CV.country;

    SELECT *
    FROM #COUNTRY_CODES;

    DROP TABLE #COUNTRY_CODES;

    This runs faster on my test runs, because it only requires one scan of the table and one sort.
    WITH CODE_VALUES AS
    (
        SELECT cc.country, cc.code, MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) AS countrycode
        FROM #COUNTRY_CODES CC
    )
    UPDATE CV
    SET cv.code = cv.countrycode
    FROM CODE_VALUES AS CV
    WHERE cv.code = 'checklater';

    Drew

    CREATE TABLE #COUNTRY_CODES (
      country varchar(10),
      code varchar(10)
    );

    INSERT INTO #COUNTRY_CODES (country, code)
    VALUES  ('australia', '55555'),
       ('australia', '55555'),
       ('australia', 'checklater'),
       ('ukraine', '33333'),
       ('ukraine', 'checklater'),
       ('ukraine', 'checklater'),
       ('ukraine', '33333'),
       ('america', '22222'),

            -----------new condition-------------
            ('nigeria', 'checklater'),
            ('nigeria', 'checklater'),

            ('korea', '66666'),
            ('korea', '66666'),
            ('korea', 'checklater'),
            ('korea', '22222')

    SELECT *
    FROM #COUNTRY_CODES;

    i have a new condition, is it possible to apply in one query. I am doing an update on every each condition looks messy.

    Above new condition, if found same country with only checklater, replace to 'NEW' (sample on country=nigeria).
    if found county with checklater, and more than one different code, replace to the one which has more than one code (sample on country=korea)

    Expected result:
    country code
    australia 55555
    australia 55555
    australia 55555
    ukraine 33333
    ukraine 33333
    ukraine 33333
    ukraine 33333
    america 22222
    nigeria NEW
    nigeria NEW
    korea 66666
    korea 66666
    korea 66666
    korea 66666

    The first one is a simple change to the query that I provided.  If you understand what the code is doing, you should be able to predict what the result for Nigeria will be, and thus how to produce your desired results.  Try it for yourself, and post your changes here.

    The second one isn't fully defined.  Specifically, what do you want to do in the case where there are multiple codes for a country and the all appear on more than one record?

    Drew

    I did something like this for first one.

    WITH CODE_VALUES AS
    (
      SELECT cc.country, cc.code
        ,case when MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) is null then 'NEW'
         else MAX(NULLIF(cc.code, 'checklater')) OVER(PARTITION BY cc.country) end AS countrycode
      FROM #COUNTRY_CODES CC
    )
    UPDATE CV
    SET cv.code = cv.countrycode
    FROM CODE_VALUES AS CV
    WHERE cv.code = 'checklater';

    for the second condition im not sure how to apply it

    ('korea', '66666'),
    ('korea', '66666'),
    ('korea', 'checklater'),
    ('korea', '22222')

    if multiple code found 66666 then apply all as 66666, if multiple code found 22222 then all apply to 22222.

    Use COALESCE() or ISNULL() for the first one instead of the CASE expression.

    For the second one, what do you want to do when the data is slightly changed?

    ('korea', '66666'),
    ('korea', '66666'),
    ('korea', 'checklater'),
    ('korea', '22222')
    ('korea', '22222')

    You said to use the one that has the most values, but 66666 and 22222 are tied for the most.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply