asked on

SQL one to many query

i have to tables one with a list of countries
countrlist table
id name
1      Afghanistan
2      Albania
3      Andorra
4      Austria

and the other table with country specfic with breakout

destination rate
Afghanistan        0.22
Afghanistan-Kabul        0.22
Afghanistan-Mobile        0.20
Afghanistan-Mobile-Areeba        0.22
Afghanistan-Mobile-Etisalat        0.19
Afghanistan-Mobile-Roshan        0.21
Albania        0.05
Albania-Mobile        0.20
Albania-Mobile-AMC        0.26
Albania-Mobile-Vodafone        0.13
Albania-Tirana        0.03
Algeria        0.05
Algeria-Algiers        0.05
Algeria-Mobile        0.18
Algeria-Mobile-Orascom        0.32
Algeria-Mobile-Wataniya        0.31
American Samoa        0.04
Andorra        0.02
Andorra-Mobile        0.18
Angola        0.11
Angola-Mobile        0.10
Angola-Mobile-Unitel        0.10
Anguilla        0.11
Anguilla-Mobile        0.18
Anguilla-Mobile-CW        0.19
Anguilla-Mobile-Digicel        0.18
Antarctic Territories        1.18
Antigua & Barbuda        0.16
Antigua & Barbuda-Mobile        0.13
Antilles        0.13
Antilles-Curacao        0.07
Antilles-Mobile        0.12
Antilles-Mobile-Curacao        0.11
Antilles-Mobile-Ecc        0.12
Antilles-St. Maarten        0.08
Armenia        0.09
Armenia Mobile-K-Telecom        0.14
Armenia-Karabakh        0.19
Armenia-Mobile        0.15
Aruba        0.09
Aruba-Mobile        0.19
Aruba-Mobile-Digicell        0.21
Aruba-Mobile-MIO        0.20
Ascension Island        1.59
Australia        0.02
Australia-Adelaide/Perth        0.01
Australia-Cities        0.01
Australia-Mobile        0.10
Australia-Mobile-Optus        0.09
Australia-MobileSat        0.10
Australia-Mobile-Telstra        0.09
Australia-Mobile-Vodafone        0.10
Australia-NGN        0.03
Austria        0.02
Austria-Mobile        0.30
Austria-Mobile-Hutchinson        0.04
Austria-Mobile-Mobilcom        0.05
Austria-Mobile-Orange        0.04
Austria-Mobile-T Mobile        0.05

i would like to be able to combine the second table destinations together based on the names of the first table

so it should show

id name info
1 afghanistan Afghanistan 0.22,Afghanistan-Kabul 0.22,Afghanistan-Mobile 0.20,Afghanistan-Mobile-Areeba 0.22,Afghanistan-Mobile-Etisalat 0.19,Afghanistan-Mobile-Roshan 0.21

etc

Aaron Shilo

select a.name , b.rate
from countrlist A join secondtable B
on a.name = b.destination

joshbula

I have had great success using the Cross Apply example from this post:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

USE AdventureWorks
GO

SELECT
CustomerID,
SalesOrderIDs = LEFT(o.list, LEN(o.list)-1)
FROM
Sales.Customer c
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), SalesOrderID) + ',' AS [text()]
FROM
Sales.SalesOrderHeader s
WHERE
s.CustomerID = c.CustomerID
ORDER BY
SalesOrderID
FOR XML PATH('')
) o (list)
ORDER BY
CustomerID

mmr159

I was thinking of using a CTE but I couldn't get it to work quickly, and now I'm being asked to work... the nerve of some employers!

For people wanting to test...

if object_id('country_rate','U') IS NOT NULL
      drop table country_rate
go

create table country_rate (
      destination varchar(256),
      rate float
      )
go

insert into country_rate (destination,rate)
select 'Afghanistan',0.22
union
SELECT 'Afghanistan-Kabul',0.22
union
SELECT 'Afghanistan-Mobile',0.20
union
SELECT 'Afghanistan-Mobile-Areeba',0.22
union
SELECT 'Afghanistan-Mobile-Etisalat',0.19
union
SELECT 'Afghanistan-Mobile-Roshan',0.21
union
SELECT 'Albania',0.05
union
SELECT 'Albania-Mobile',0.20
union
SELECT 'Albania-Mobile-AMC',0.26
union
SELECT 'Albania-Mobile-Vodafone',0.13
union
SELECT 'Albania-Tirana',0.03
union
SELECT 'Algeria',0.05
union
SELECT 'Algeria-Algiers',0.05
union
SELECT 'Algeria-Mobile',0.18
union
SELECT 'Algeria-Mobile-Orascom',0.32
union
SELECT 'Algeria-Mobile-Wataniya',0.31
union
SELECT 'American Samoa',0.04
union
SELECT 'Andorra',0.02
union
SELECT 'Andorra-Mobile',0.18
union
SELECT 'Angola',0.11
union
SELECT 'Angola-Mobile',0.10
union
SELECT 'Angola-Mobile-Unitel',0.10
union
SELECT 'Anguilla',0.11
union
SELECT 'Anguilla-Mobile',0.18
union
SELECT 'Anguilla-Mobile-CW',0.19
union
SELECT 'Anguilla-Mobile-Digicel',0.18
union
SELECT 'Antarctic Territories',1.18
union
SELECT 'Antigua & Barbuda',0.16
union
SELECT 'Antigua & Barbuda-Mobile',0.13
union
SELECT 'Antilles',0.13
union
SELECT 'Antilles-Curacao',0.07
union
SELECT 'Antilles-Mobile',0.12
union
SELECT 'Antilles-Mobile-Curacao',0.11
union
SELECT 'Antilles-Mobile-Ecc',0.12
union
SELECT 'Antilles-St. Maarten',0.08
union
SELECT 'Armenia',0.09
union
SELECT 'Armenia Mobile-K-Telecom',0.14
union
SELECT 'Armenia-Karabakh',0.19
union
SELECT 'Armenia-Mobile',0.15
union
SELECT 'Aruba',0.09
union
SELECT 'Aruba-Mobile',0.19
union
SELECT 'Aruba-Mobile-Digicell',0.21
union
SELECT 'Aruba-Mobile-MIO',0.20
union
SELECT 'Ascension Island',1.59
union
SELECT 'Australia',0.02
union
SELECT 'Australia-Adelaide/Perth',0.01
union
SELECT 'Australia-Cities',0.01
union
SELECT 'Australia-Mobile',0.10
union
SELECT 'Australia-Mobile-Optus',0.09
union
SELECT 'Australia-MobileSat',0.10
union
SELECT 'Australia-Mobile-Telstra',0.09
union
SELECT 'Australia-Mobile-Vodafone',0.10
union
SELECT 'Australia-NGN',0.03
union
SELECT 'Austria',0.02
union
SELECT 'Austria-Mobile',0.30
union
SELECT 'Austria-Mobile-Hutchinson',0.04
union
SELECT 'Austria-Mobile-Mobilcom',0.05
union
SELECT 'Austria-Mobile-Orange',0.04
union
SELECT 'Austria-Mobile-T Mobile',0.05

mmr159

Oops... forgot the country list

if object_id('country_list','U') IS NOT NULL
      drop table country_list
go

create table country_list (
      id int identity(1,1),
      name varchar(256)
      )
go

insert into country_list (name)
select 'Afghanistan'
union
select 'Albania'
union
select 'Andorra'
union
select 'Austria'
go

daz1234

ASKER

hi joshbula

how can i apply your solution

ASKER CERTIFIED SOLUTION

joshbula

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

daz1234

ASKER

cross apply is only available on sql 2005 i'm using an earlier sql version it there any other way to write this script

joshbula

SqlServerCentral just did an great article on this in their newsletter this morning.

http://www.sqlservercentral.com/articles/T-SQL/67973/

You'll have to adapt it to your tables, but it might be a good place to start to understand the concept.

daz1234

ASKER

thanks had to upgrade to sql 2008 and worked like a dream