Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL one to many query

Posted on 2011-03-03
9
Medium Priority
?
290 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:daz1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35027910
select a.name , b.rate
from   countrlist  A join secondtable B
on a.name = b.destination                              
0
 
LVL 9

Expert Comment

by:joshbula
ID: 35027915
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
 
LVL 7

Expert Comment

by:mmr159
ID: 35028378
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:mmr159
ID: 35028418
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
 

Author Comment

by:daz1234
ID: 35029357
hi joshbula

how can i apply your solution
0
 
LVL 9

Accepted Solution

by:
joshbula earned 2000 total points
ID: 35030769
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
 

Author Comment

by:daz1234
ID: 35031195
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
 
LVL 9

Expert Comment

by:joshbula
ID: 35036037
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
 

Author Closing Comment

by:daz1234
ID: 35043032
thanks had to upgrade to sql 2008 and worked like a dream
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question