To Retrieve Data From Parent-child Tables In A Flat Data Set

Due to a bad existing DB schema design, I have to write a select statement in a special way as shown in this thread. SELECT MAX(CASE WHEN Name = 'C' THEN RelativePath END) AS C,

Solution 1:

SELECT
  Parent.third_party_id,
  Parent.createdBy,
  Parent.createdDate,
  Child.C,
  Child.CC,
  Child.S,
  Child.SC,
  Child.R,
  Child.RC
FROM
  Parent
LEFT OUTER JOIN
(
  SELECT 
     ID,
     MAX(CASE WHEN Name = 'C' THEN RelativePath END) AS C,
     MAX(CASE WHEN Name = 'CC' THEN RelativePath END) AS CC,
     MAX(CASE WHEN Name = 'S' THEN RelativePath END) AS S,
     MAX(CASE WHEN Name = 'SC' THEN RelativePath END) AS SC,
     MAX(CASE WHEN Name = 'R' THEN RelativePath END) AS R,
     MAX(CASE WHEN Name = 'RC' THEN RelativePath END) AS RC
  FROM myTable
  GROUP BY ID
) Child ON
  Child.ID = Parent.ID
WHERE
  Parent.ID = pId;