select
	resnum,
	GroupLeaderResNum,
	guestname,
	RoomType,
	Property,
	ResStatus
	into #Temptest
from PMSRes
	where roomtype = 'N/RBT'
	and ResStatus <> 9
Order By Property, GroupLeaderResNum

DECLARE		  @cols		AS NVARCHAR(MAX),
			  @query    AS NVARCHAR(MAX)

select
			  @cols = STUFF((SELECT ','
          + QUOTENAME(case
          when d.col = 'DiveRes' then col+cast(seq as varchar(10))
          else 'DiveRes'+cast(seq as varchar(10))+'_'+col end)
                    from
                    (
                      select row_number() over(partition by GroupLeaderResNum
                                                order by ResNum) seq
                      from #Temptest
                    ) t
                    cross apply
                    (
                      select 'DiveRes', 1 union all
                      select 'Name', 2
                    ) d (col, so)
                    group by col, so, seq
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT GroupLeaderResNum, ' + @cols + '
            from
            (
              select t.GroupLeaderResNum,
                col = case
                 when c.col = ''DiveRes'' then col+cast(seq as varchar(10))
                  else ''DiveRes''+cast(seq as varchar(10))+''_''+col
                end,
                value
              from
              (
                select GroupLeaderResNum, ResNum, GuestName,
                  row_number() over(partition by GroupLeaderResNum
                                    order by ResNum) seq
                from #TempTest
              ) t
              cross apply
             (
                select ''DiveRes'', Resnum union all
                select ''Name'', GuestName
              ) c (col, value)
            ) x
            pivot
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

drop table #Temptest