Solved
How to combine multiple entry sub fields into a single row via an SQL select
Posted on 2006-11-06
I an application that was normalized so that now a single entry resides in 23 tables. I need a query that will return all of these in a single row (comma sperated for the multi value entries).
For the sake of argument say I have 4 tables.
LOCATION: (1 Per Account)
Location_Code - Primary Key
Location Name
USER: (1 per account)
Location_Code - Foreign Key
User_ID - promary Key
User_Name
User_SSN
User_Address
User_Zip
User_Phone
Vehicle: (0 to many entries)
Location_Code - Foreign Key
User_ID - Foreign key
Veh_ID - Primary Key
Veh_Make
Veh_Model
Magazines: (0 to many entries)
Location_Code - Foreign Key
User_ID - Foreign key
Mag_ID - Primary key
Mag_Name
Mag_Subscription
What I want is a select statement that will return:
Location, User_Name, User_SSN, User_Address, User_ZIP, User_Phone, Veh_Make Veh_Model, Mag_Name Mag_Subscription
So, A single user with multiple cars and magazines might look like:
Florida, Scott, 333-44-3445, 1234 my street, 23221, 555-5656, Ford Bronco; Chevy Trailblazer, Time anual; People Anual; TV Guide Anual
Thanks!!