Data Retrievial

1

Hi

I have a below query to retrieve travellers information

select policynumber,Travellers from Policy
inner join Quote
on quote.QuoteId = policy.QuoteId

cross apply (
select PolicyTraveller.title +''+ '.'+PolicyTraveller.firstname +''+ ' '+PolicyTraveller.lastname +', '+ cast (PolicyTraveller.Age as varchar (3))+'; ' as [text()]
from PolicyTraveller
where quote.QuoteId = PolicyTraveller.QuoteId
order by PolicyTraveller.FirstName
for xml path ('')
)policytraveller (Travellers)

The above query is reverting policytravellers data like below

Travellers
Miss.Rox New, 20;
Mrs.car Emily 35; Miss.lily Alex 30;

Expected Output

I want the above to display in separate column ( no of travellers differ per policy)

Title Firstname Lastname Age Title Firstname Lastname Age
Miss Rox New 20
Mrs Car Emily 35 Miss Lily Alex 30

How to amend the above query? Any help will be highly appreciated.

Thanks

Regards,
SG

2

If you can post create table statements for the tables involved and insert into statements to populate them, we can take a look at it