Sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sql query

Author

Topic

kyledunn
Starting Member

7 Posts

Posted - 2003-03-28 : 08:44:31

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

Can you show me what the query would look like to accomplish this? Thanks for any help!

Kyle

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-28 : 09:16:55

Kyle --
today is your lucky day.

The only caveat to what I am posting below is you know the maximum # of columns that any customer can have.

Take a look:

select cus#,
max(case when LocNumber =1 then location else null end) as Loc1,
max(case when LocNumber =2 then location else null end) as Loc2,
max(case when LocNumber =3 then location else null end) as Loc3,
max(case when LocNumber =4 then location else null end) as Loc4
.. etc ...
from
(
select cus#, location, (select count(*) from tbl where tbl.cus# = a.cus# and tbl.location <= a.location) as LocNumber
from
tbl a
)
b
group by cus#

That should return exactly what you are looking for ... but make sure you have enough columns to handle all cases.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-28 : 09:49:13

I registered for email notification to see if anyone had a solution.

I'd imagined adding a column with a counter. Jeff's solution avoided altering the table.

Nice work

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources