Query for Flat name and Address
-
I need to output a flat name and address file. Names are in 1 table, and addresses are in a second table with a varying number of address rows per account. It looks like this:
tblNames
ID
Name
1
Joe Smith
2
Bob Jones
tblAddresses
ID
SeqNO
AddressField
1
1
1
2
Apt 4
1
3
Anywhere, US 40404
2
1
2
2
Desired SQLOutput
ID
Name
Address1
Address2
Address3
1
Joe Smith
Anywhere, US 40404
2
Bob Jones
-
SELECT n.[ID],n.[Name],
MAX(CASE WHEN a.SeqNo = 1 THEN a.AddressField ELSE '' END) AS [Address1],
MAX(CASE WHEN a.SeqNo = 2 THEN a.AddressField ELSE '' END) AS [Address2],
MAX(CASE WHEN a.SeqNo = 3 THEN a.AddressField ELSE '' END) AS [Address3]
FROM tblNames n
INNER JOIN tblAddresses a
ON a.[ID] = n.[ID]
GROUP BY n.[ID],n.[Name]
ORDER BY n.[ID]
This will work there are upto 3 adresses but can be expanded for more
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you sir....Thank u very much..
U r simply the GREAT & BEST...
Actually i m working on SQL Server 2000 First time and i don't know abt PL/SQL
again thanks.
-
David:
Will you please explain to me why you use max here ? Thx.
-
The result of the pivot without GROUPING will produce the following
ID Name Address1 Address2 Address3 1 Joe Smith 400 Main St 1 Joe Smith Apt 4 1 Joe Smith Anywhere, US 40404 2 Bob Jones 777 Maple Ln. 2 Bob Jones Boston, MA ostalCode w:st="on">01201ostalCode> To reduce this to one line per ID we can GROUP on ID and Name but we require only one value from each address column
To get one value of each address data we have to use aggregation, to aggregate char data we use MIN or MAX.
For ID=1 the MIN value for Address1 is '', the MAX value is '400 Main St', so by using MAX we are effectively ignoring blank values.
Far away is close at hand in the images of elsewhere.
Anon. -
One picture is worth a thousand words... great explanation...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply