You need to modify the sub-query that you use for your source so that it has all columns that will be used for your PIVOT, like this:
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ', ','') + QUOTENAME(EnquiryPeriod)
FROM (SELECT DISTINCT EnquiryPeriod FROM Enquiries) AS EnqPeriod
SET @DynamicPivotQuery =
N'SELECT ChildID, [childName], ' + @ColumnName + '
FROM (SELECT Children.ChildID, [Firstname] + [Surname] as [childName],
Enquiries.EnquiryPeriod, EnquiryDate
FROM Enquiries
INNER JOIN CarerChildren ON CarerChildren.CarerID = Enquiries.CarerID)
INNER JOIN Children ON CarerChildren.ChildID = Children.ChildID
WHERE CarerChildren.DateMovedOn Is Null) AS pvtSource
PIVOT(Max(EnquiryDate)
FOR EnquiryPeriod IN (' + @ColumnName + ')) AS PVTTable'
EXEC (@DynamicPivotQuery)