SQL:SQL query challenge

[kyledunn]

I have two tables that have been joined into a single table. The first table contains primary key Customer Number and Customer Name. The second table contains a foreign key Customer Number and Location Number. There can be multiple locations for each customer. When I join these two tables with

Select Table1.CustomerNumber, Table1.CustomerName, Table2.LocationNumber from Table1, Table2 where Table1.CustomerNumber = Table2. CustomerNumber

I get a new table with three columns, Customer Number Customer Name and Location with a repeating Customer Number and Name for each location.

Is it possible with a query to turn this into a table with a single row for each customer number and name with a column for each location? The total number of columns would be determined by the customer with the most locations and those with fewer locations would have a null value for the unfilled columns.

Here is the example results:

Start with:

Cus# Name Location
1 John 1
1 John 2
1 John 3
2 Mike 4
2 Mike 5
3 Bob 6

With miracle query:

Cus# Name Loc1 Loc2 Loc3
1 John 1 2 3
2 Mike 4 5 null
3 Bob 6 null null

Thanks for any help!

Kyle

[jkoopmann]

kyle,
don't know what database you are using.
i am basing my reply on oracle and going out on a limb here

seems to me you are asking to have a sql statement that dynamically detects what you want to have in your select list. this i believe can not be done in standard sql. you will need to have some form of an application, function, procedure, ... that produces a dynamic select list for you.

all that being said, if you know the number of locations you could do something like the following to produce somewhat what you are asking.

select customer_number, customer_name,
sum(decode(location_number,1,1,null))loc1,
sum(decode(location_number,2,2,null))loc2,
sum(decode(location_number,3,3,null))loc3,
sum(decode(location_number,4,4,null))loc4,
sum(decode(location_number,5,5,null))loc5,
sum(decode(location_number,6,6,null))loc6,
sum(decode(location_number,7,7,null))loc7,
sum(decode(location_number,8,8,null))loc8,
sum(decode(location_number,9,9,null))loc9,
sum(decode(location_number,10,10,null))loc10
from ( Select Table1.CustomerNumber customer_number,
Table1.CustomerName customer_name,
Table2.LocationNumber location_number
from Table1, Table2
where Table1.CustomerNumber = Table2. CustomerNumber )
group by customer_number, customer_name

should get something like this:
CUSTOMER_NUMBERCUSTOMER_N LOC1 LOC2 LOC3 LOC4 LOC5 LOC6 LOC7 LOC8 LOC9 LOC10
--------------- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
1 John 1 2 3
2 Mike 4 5
3 Bob 6