How to handle sticks on column country1 and country2 columns ?

  • I work on sql server 2017 i have table data as below

    i need to make design for data to be best practise and prevent repeating data

    my issue here on column countries1 and countries2 columns have data separated sticks so how to handle that

    so are making design for countries table or what

     create table #countriesData
    (
    company int,
    [Year] int,
    rev int,
    countries1 varchar(500),
    countries2 varchar(500)
    )
    insert into #countriesData(company,[Year],rev,countries1,countries2)
    values
    (12011,2010,121,'Egypt|France|America','India|France|America'),
    (12011,2011,121,'Egypt|Canda|America','India|Indonisya|America'),
    (12011,2012,121,'China|Canda|America','Pakistan|Indonisya|America'),
    (12099,2010,121,'SaudiArabia|France|America','Pakistan|sryia|America'),
    (12099,2011,121,'Egypt|Canda|German','Pakistan|Saudia|America'),
    (12099,2012,121, 'China|Italy|America','Holanda|Saudia|America')

     

    my key is company and year and rev and not repeated

    so How to handle sticks on column countries

  • What do you mean by "sticks" in this question? There is nothing in you question or the DDL or data to explain that.

  • the OP means the pipe "|" between strings

  • Sticks = pipes ("|").  Thank you, Frederico! I guess I have heard that term, but not as commonly as pipe, and didn't make the connection here.

    Ahmed: Questions --

    • What do countries1 & countries2 represent? How are they different? Could there someday be countries3?
    • Why the denormalized table with delimited lists and more than one country/countries column? Why not use an association table for countries (with a type column to handle the need for multiple countries columns)?
    • Why not use ISO country codes instead of names? The smaller strings might allow for more efficient indexing, and avoid risks of variations in spelling leading to failing to find data in selects, joins.

    If you must split-out delimited lists (e.g, that's how a vendor sends the data), use the STRING_SPLIT function, which will turn the list into a table-valued-variable.

     

     

  • Your structure should be normalized, as you stated.

    create table #countries

    (

    company int,

    [year] int,

    rev int,

    country_sequence smallint,

    country varchar(100)

    )

    (12011,2010,121,1,'Egypt')

    (12011,2010,121,2,'France')

    ,,,

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ahmed_elbarbary.2010 wrote:

    my key is company and year and rev and not repeated

    What, from your example code, would constitute "repeating data"?  If the answer is "none of it", then we need an example of what would constitute "repeating data", please.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This was removed by the editor as SPAM

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

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