Customer locality..?

[kenjoswe]

Hi all,

I want to create a report that shows where we have our customers.

I have a table that looks like this:

CustId CustName Locality
1 A A
2 A B
3 A C
4 B A
5 B B
6 C A


I want to create a report that look like this:
CustName Locality1 Locality2 Locality3 LocalityX
A A B C
B A B
C A

How can I write the correct SQL-syntax for this?


/Kent J.

 

[grahamm]

I don't know general sql stmt that will work if LocalityX is not known a priori. However if it is known e.g. the only localities are A,B,C then you can write sql such as:

select distinct z.CustName,
Locality1=a.Locality,
Locality2=b.Locality,
Locality3=c.Locality
from geo z, geo a , geo b, geo c
where a.Locality = 'A' and
b.Locality = 'B' and
c.Locality = 'C' and
z.custname *= a.custname and
z.custname *= b.custname and
z.custname *= c.custname

or if you prefer the explicit outer join syntax (SQL-92) then:

select distinct z.CustName,
Locality1= a.Locality,
Locality2=b.Locality,
Locality3=c.Locality
from geo z left join
(select * from geo where Locality = 'A') a
on z.custname = a.custname left join
(select * from geo where Locality = 'B') b
on z.custname = b.custname left join
(select * from geo where Locality = 'C') c
on z.custname = c.custname

A general solution that is going to work for a number of localities that is not known a priori is going to require a stored procedure that builds the sql stmt and then exec's it.

 

[kenjoswe]

Grahamm,
Thanks! But what if a single customer can be on max three different places (sites) but the total number of different places are thousand or more (as in the real world)?

/Kent J.

 

[tlbroadbent]

There are other ways to accomplish this with dynamic SQL. Some SQL Sever T-SQL solutions are available at

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

http://www.swynk.com/sqlscripts/misc7.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.