Column w/ Multiple Issues

[OU18]

Hi all,

I have an issue with some data that is sent to me from another company. I work in telecom, and we recv rate changes from vendors. Some weekly, some daily, some monthly. There really is no rhyme or reason to the way they come in.

The issue I am running into is, there are multiple vendors that like to send their data in the following format.

table name - tblInternationalRates

this is an example
Country NPA Country Code CityCode
UK 44 207,208
UK 44 7903,7904,7905

What we need to do is take the City code column and create a new record for each Code. So instead of the above you would have

Country NPA Country Code City Code
UK 44 207
UK 44 208
UK 44 7903
UK 44 7904
Etc.

We have tried to get the vendors to send in this format, but there is alot of resistance. We need to change the data to the format our Billing Platform can use.

Thanks for the help

 

[dRahme]

Hi, kind of hard to tell if this is appropriate for your example, but you might play around with a union query. Something like:

SELECT tbl1.Country,tbl1.CountryCode,tbl1.NPA
FROM tbl1
UNION Select tbl1.country, tbl1.citycode,tbl1.NPA from tbl1;