Well, you DO have something to aggregate...

Attached please find tow code snippets:

the first one is without any aggregation. As you'll see, you end up with the same number of rows like the original table, each row holding just one value beside the people_code_id.

But you only want to have one row per people_code_id. Therefore, you'd need to aggregate your values.

With text data, you usually use MAX(), as shown in the second code snippet.

I agree, it's easier to "get the concept" if you see data based on your original sample. So I wrote the first part of the CrossTab query you'll need.

SELECT

people_code_id,

CASE WHEN RECORD_NUM = 1 THEN NAME ELSE NULL END AS CHILD1,

CASE WHEN RECORD_NUM = 2 THEN NAME ELSE NULL END AS CHILD2

FROM RELATIONSHIP

SELECT

people_code_id,

max(CASE WHEN RECORD_NUM = 1 THEN NAME ELSE NULL END) AS CHILD1,

max(CASE WHEN RECORD_NUM = 2 THEN NAME ELSE NULL END) AS CHILD2

FROM RELATIONSHIP

GROUP BY people_code_id