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

daz1234Asked:
Who is Participating?
 
joshbulaConnect With a Mentor Commented:
SELECT
    ID, name, destination = LEFT(o.list, LEN(o.list)-1)
FROM
    countrlist  
CROSS APPLY
(
        SELECT
            destination + '-' + rate +',' AS [text()]
        FROM
            destinationTable
        WHERE
         countrlist.ID = destinationTable.CountryID      ///(or whatever the foreign key is that you can join it to the country list table.  If you don't have one, you might have to use LIKE )
        ORDER BY
            destination
        FOR XML PATH('')
    ) o (list)
ORDER BY
    ID
0
 
Aaron ShiloChief Database ArchitectCommented:
select a.name , b.rate
from   countrlist  A join secondtable B
on a.name = b.destination                              
0
 
joshbulaCommented:
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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mmr159Commented:
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
0
 
mmr159Commented:
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
0
 
daz1234Author Commented:
hi joshbula

how can i apply your solution
0
 
daz1234Author Commented:
cross apply is only available on sql 2005 i'm using an earlier sql version it there any other way to write this script

0
 
joshbulaCommented:
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.
0
 
daz1234Author Commented:
thanks had to upgrade to sql 2008 and worked like a dream
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.