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 8 hours 47 minutes ago

try this... you could dynamic pivot also...

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'


;with CTE AS (Select A.Id,A.StartDate,A.EndDate,B.StartDate as [ChildStartDate],B.ChildTypeId,B.Id as [ChildID],
Row_number() over (partition by a.id order by B.id asc) as R1
from @parent A inner join @child B on A.Id=B.ParentId)
--Select max(R1) from Cte
--the output of this should be no of case statements you ----have to write
Select Id,StartDate,EndDate,
max(Case When R1=1 then ChildTypeId End) ChildTypeId1,
max(Case When R1=1 then ChildStartDate End) ChildStartDate1,
Max(Case When R1=2 then ChildTypeId End) ChildTypeId2,
Max(Case When R1=2 then ChildStartDate End) ChildStartDate2,
Max(Case When R1=3 then ChildTypeId End) ChildTypeId3,
Max(Case When R1=3 then ChildStartDate End) ChildStartDate3,
Max(Case When R1=4 then ChildTypeId End)ChildTypeId4,
Max(Case When R1=4 then ChildStartDate End)ChildStartDate4
 from CTE
 group by Id,StartDate,EndDate

>> 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 child_type_id [sic], c.something_start_date, c.something_end_date being contained in the parent [sic] row. <<

We use ISO-8601 date formats in SQL. So do the rest of the ISO standards and IT. Not knowing it is like being an engineer who does nto use the Metric system. 

The terms parent and child are not RDBMS; that was how we did this in 1970's network databases. We have weak and strong entities and referenced and referencing  tables. This is enforced by DRI, which you left out completely. 

There is no such crap as a type_id; the attribute can be a type or an id, but not this. Do you have a blood_type_id? You do not know ISO-11179 rules. Since tables are sets, their names are plural or collective nouns. 

Let's make the strong entity a project, with a weak task entity. Since you do no math with identifiers, they are never numeric, but it makes them look like pointers from your old network models. 

CREATE TABLE Projects 
(project_name CHAR(3) NOT NULL PRIMARY KEY, 
 project_start_date DATE NOT NULL, 
 project_end_date DATE NOT NULL,
 CHECK(project_start_date <= project_end_date));

We now need DRI and compound key in the referencing table. This is one of the most common SQL idioms and you should know it. 

CREATE TABLE Tasks 
(project_name CHAR(3) NOT NULL
 REFERENCES Projects(project_name)
 ON DELETE CASCADE,
 task_name CHAR(2) NOT NULL, 
 PRIMARY KEY (project_name, task_name),
 task_type char(2) NOT NULL, 
 task_start_date DATE NOT NULL, 
 task_end_date DATE NOT NULL,
  CHECK(task_start_date <= task_end_date));

INSERT INTO Projects 
VALUES ('001', '2015-01-01', '2015-12-31'),
   ('002', '2016-01-01', '2016-12-31'),
   ('003', '2017-01-01', '2017-12-31'),
   ('004', '2017-01-01', '2017-12-31');

INSERT INTO Tasks
VALUES
 ('001', 'AA', '2015-01-01', '2015-03-31'),
 ('002', 'AA', '2015-04-01', '2015-06-30'),
 ('003', 'AA', '2015-07-01', '2015-09-30'),
 ('004', 'AA', '2015-10-01', '2015-12-31'),
 ('001', 'BB', '2016-01-01', '2016-03-31'),
 ('002', 'BB', '2016-04-01', '2016-06-30'),
 ('003', 'BB', '2016-07-01', '2016-09-30'),
 ('004', 'BB', '2016-10-01', '2016-12-31'),
 ('001', 'CC', '2017-01-01', '2017-03-31'),
 ('002', 'CC', '2017-04-01', '2017-06-30'),
 ('003', 'CC', '2017-07-01', '2017-09-30'),
 ('004', 'CC', '2017-10-01', '2017-12-31'),
 ('001', 'DD', '2018-01-01', '2018-03-31'),
 ('002', 'DD', '2018-04-01', '2018-06-30'),
 ('003', 'DD', '2018-07-01', '2018-09-30'),
 ('004', 'DD', '2018-10-01', '2018-12-31');

You said you wanted:

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

The idea of putting everything in one row is absurd! This violates First Normal Form. Why are you trying to do the wrong things, the wrong way?