Pfthb

Here is the table structure

ID TypeX TypeXDesc XDate TypeCodeY
040001 3669 Unspecified Cat 2005-08-08 1
040001 3669 Unspecified Cat 2006-08-29 2
040001 37515 Tear Film 2005-08-08 1
040001 37999 Disor 2004-07-22 1

Transform above table INTO below USING PIVOT

ID TypeX_1 TypeXDesc_1 XDate_1 TypeCodeY_1 TypeX_2 TypeXDesc_2 XDate_2 TypeCodeY_2 TypeX_3 TypeXDesc_3 XDate_3 TypeCodeY_3
040001 3669 Unspecified Cat 2005-08-08 1 37515 Tear Film 2005-08-08 1 37999 Disor 2004-07-22 1

Look at the same TypeX code but XDate is different and we need to get Min(XDate) so first row is qualified not the second row.

You can accomplish this using conditional aggregation. In this case, you can enumerate the rows with within typex/id groups using row_number(). You can enumerate the groups with with typex/id using dense_rank().

Then, use conditional aggregation:

select t.id,
 max(case when grpnum = 1 and seqnum = 1 then typex end) as typex_1,
 max(case when grpnum = 1 and seqnum = 1 then TypeXDesc end) as TypeXDesc_1,
 max(case when grpnum = 1 and seqnum = 1 then XDate end) as XDate_1,
 max(case when grpnum = 1 and seqnum = 1 then TypeCodeY end) as TypeCodeY_1,
 max(case when grpnum = 2 and seqnum = 1 then typex end) as typex_12,
 max(case when grpnum = 2 and seqnum = 1 then TypeXDesc end) as TypeXDesc_2,
 max(case when grpnum = 2 and seqnum = 1 then XDate end) as XDate_2,
 max(case when grpnum = 2 and seqnum = 1 then TypeCodeY end) as TypeCodeY_3,
 max(case when grpnum = 3 and seqnum = 1 then typex end) as typex_1,
 max(case when grpnum = 3 and seqnum = 1 then TypeXDesc end) as TypeXDesc_3,
 max(case when grpnum = 3 and seqnum = 1 then XDate end) as XDate_3,
 max(case when grpnum = 3 and seqnum = 1 then TypeCodeY end) as TypeCodeY_3
from (select t.*,
 row_number() over (partition by id, typex order by xdate as seqnum,
 dense_rank() over (partition by id order by typex) as grpnum
 from t
 ) t
group by id;