How to handle sticks on column country1 and country2 columns ?

Question

Post reply

How to handle sticks on column country1 and country2 columns ?

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

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

ratbak Default port Points: 1423 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: 16279 More actions · Answer 2

frederico_fonseca

SSCoach

Points: 16279

May 23, 2022 at 1:53 pm

#4037637

the OP means the pipe "|" between strings

ratbak Default port Points: 1423 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: 101207 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004664 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)