How to handle sticks on column country1 and country2 columns ?

Question

Post reply

How to handle sticks on column country1 and country2 columns ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

May 22, 2022 at 11:51 pm

#4037283

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

May 24, 2022 at 4:39 am

This was removed by the editor as SPAM

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

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

ratbak Ten Centuries Points: 1335 More actions · Answer 1

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

frederico_fonseca SSCoach Points: 16037 More actions · Answer 2

frederico_fonseca

SSCoach

Points: 16037

May 23, 2022 at 1:53 pm

#4037637

the OP means the pipe "|" between strings

ratbak Ten Centuries Points: 1335 More actions · Answer 3

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.

ScottPletcher SSC Guru Points: 100951 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

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)