April 20, 2011 at 11:06 am
I am trying to create a query from a parent/child table that has a 1:M relationship. I created a join statement but if there are 3 records in the child table then each parent record is triplicated. I want to create a statement that will return one row with all of the data.
Parent Table
ID
Name
Street
City
State
Zip
Phone
Child Table
Child ID
Parent ID
Child Type
Value 1
Value 2
There are 3 different Child Types and each Parent record has 3 child records
I want a statement that returns
ID, Name, Street, City, State, Zip, Phone, Child Type 1 Value 1, Child Type 1 Value 2, Child Type 2 Value 1, Child Type 2 Value 2, Child Type 3 Value 1, Child Type 3 Value 2
I created this SQL Statement:
select
Parent.id AS 'ID',
Parent.Name AS 'Name',
Parent.Address1 AS 'Address',
Parent.City AS 'City',
Parent.State AS 'State',
Parent.Zip AS 'Zip',
Parent.Phone AS 'Phone',
Child.value1,
Child.value2
from Parent
left outer join Child on Child.id = Parent.id
where Parent.active=1 and Parent.deleted = 0
There is also a 3rd table that I am joining but I ommited it for simplicities sake.
Unfortunately this returns 3 records for each parent record. How can I flatten it to only 1 record per parent record? I need to export this data into a CSV file so it must be 1 record per parent record.
I have looked into Pivot but haven't been able to wrap my head around how to accomplish what I am wanting or if that is even the correct tool for what I am trying to accomplish.
Any help would be appreciated.
Thanks!
April 20, 2011 at 2:25 pm
A cross-tab query should do what you need, PIVOT will too, although cross-tabs are generally accepted as the better performer. Here is a great article explaining and comparing the two: http://www.sqlservercentral.com/articles/T-SQL/63681/%5B/url%5D
Please provide some DDL to create your tables and DML to create test data in those tables so we can better assist. If you do not know what I mean please read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply