May 22, 2022 at 11:51 pm
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 23, 2022 at 1:38 pm
What do you mean by "sticks" in this question? There is nothing in you question or the DDL or data to explain that.
May 23, 2022 at 1:53 pm
the OP means the pipe "|" between strings
May 23, 2022 at 2:12 pm
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 --
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.
May 23, 2022 at 3:10 pm
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!
May 23, 2022 at 6:14 pm
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
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