Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row.  So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:

1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31

Thanks for any help provided. 

declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)

insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
insert @child select 2, 2, 'AA', '4/1/2015', '6/30/2015'
insert @child select 3, 3, 'AA', '7/1/2015', '9/30/2015'
insert @child select 4, 4, 'AA', '10/1/2015', '12/31/2015'
insert @parent select 2, '1/1/2016', '12/31/2016'
insert @child select 5, 1, 'BB', '1/1/2016', '3/31/2016'
insert @child select 6, 2, 'BB', '4/1/2016', '6/30/2016'
insert @child select 7, 3, 'BB', '7/1/2016', '9/30/2016'
insert @child select 8, 4, 'BB', '10/1/2016', '12/31/2016'
insert @parent select 3, '1/1/2017', '12/31/2017'
insert @child select 9, 1, 'CC', '1/1/2017', '3/31/2017'
insert @child select 10, 2, 'CC', '4/1/2017', '6/30/2017'
insert @child select 11, 3, 'CC', '7/1/2017', '9/30/2017'
insert @child select 12, 4, 'CC', '10/1/2017', '12/31/2017'
insert @parent select 4, '1/1/2018', '12/31/2018'
insert @child select 13, 1, 'DD', '1/1/2018', '3/31/2018'
insert @child select 14, 2, 'DD', '4/1/2018', '6/30/2018'
insert @child select 15, 3, 'DD', '7/1/2018', '9/30/2018'
insert @child select 16, 4, 'DD', '10/1/2018', '12/31/2018'


select p.Id, p.StartDate, p.EndDate, c.ChildTypeId, c.StartDate, c.EndDate
from @parent p
inner join @child c on c.ParentId = p.Id
where ParentId = 1

• Edited by Mike Smith1_1 Tuesday, May 19, 2015 10:19 PM

Dear --CELKO--:  Once again, you can take your condescending, snotty, arrogant, snippy, whiny, good-for-nothing answer and stick it up your ANSI.  This time, I'm not even going to bother answering your idiocy.  I will simply refer others to this so they can avoid your worthless answers in the future.

I appreciate your point regarding Joe, but he is right. Sticking to the tandards will make your life, and those that work with you, or after you, easier.

He comes off as harsh and mean, but that's mostly due to countless encounters over a lot of years, with people doing things the wrong way.

With that said, try this and see if it gets you close to what you need:

insert @child select 7, 3, 'BB', '7/1/2016', '9/30/2016'
insert @child select 8, 4, 'BB', '10/1/2016', '12/31/2016'
insert @parent select 3, '1/1/2017', '12/31/2017'
insert @child select 9, 1, 'CC', '1/1/2017', '3/31/2017'
insert @child select 10, 2, 'CC', '4/1/2017', '6/30/2017'
insert @child select 11, 3, 'CC', '7/1/2017', '9/30/2017'
insert @child select 12, 4, 'CC', '10/1/2017', '12/31/2017'
insert @parent select 4, '1/1/2018', '12/31/2018'
insert @child select 13, 1, 'DD', '1/1/2018', '3/31/2018'
insert @child select 14, 2, 'DD', '4/1/2018', '6/30/2018'
insert @child select 15, 3, 'DD', '7/1/2018', '9/30/2018'
insert @child select 16, 4, 'DD', '10/1/2018', '12/31/2018'

SELECT ParentId, pStartDate, pEndDate, MAX(AA) AS AAStart, MAX(BB) AS BBStart, MAX(CC) AS CCStart, MAX(DD) AS DDStart, MAX(AAE) AS AAEnd, MAX(BBE) AS BBEnd, MAX(CCE) AS CCEnd, MAX(DDE) AS DDEnd
  FROM (
        SELECT c.id, c.parentID, p.startDate AS pStartDate, p.EndDate AS pEndDate, c.ChildTypeId AS sChildTypeId, c.ChildTypeId+'E' AS eChildTypeId, c.StartDate AS cStartDate, c.EndDate AS cEndDate
          FROM @parent p
            INNER JOIN @child c
        	  ON p.Id = c.ParentId
         --ORDER BY p.id
       ) s
    PIVOT (
	       MAX(cStartDate) FOR schildTypeID IN ([AA],[BB],[CC],[DD])
		  ) p
    PIVOT (
	       MAX(cEndDate) FOR echildTypeID IN ([AAE],[BBE],[CCE],[DDE])
		  ) p2
 GROUP BY ParentId, pStartDate, pEndDate

Thanks Patrick, this is what I was looking for.

And, with all due respect to you, I'm sure Joe does have countless encounters with those who do things the wrong way, but that in no way entitles him to act like the backside of a horse.  If I'd have said, "here take a look at my production database tables" then there would be a point to be had about standards, but when people simplify things greatly to just get a simple point across and get an answer?  I don't need to be lectured about standards for a 20 line forum post.