quote:

---

Originally posted by satheesh

Dear All,

I have a policy table and traveller table. I need to display all policy number and traveller name.

Now if there are multiple travellers in a policy it gets display in separate row.But I need to display in a single row

For eg
Policy number 1212212 has 2 travellers now I am getting output like

Policynumber FirstName Last Name
1212212 John Helen
1212212 Jermmy Wagg

Excepted result be
PolicyNumber FirstName LastName FirstName LastName
1212212 John Helen Jermmy Wagg

If there are many travellers (for eg 5)then all travellers firstname and lastname need to display in separate Column in a single row

Please any one can suggest me how to modify the below query

Select policy.policynumber,traveller.firstname, traveller.lastname
From policy
inner join traveller
On policy.policyid = traveller.policyid

Any help regarding this is highly appreciated.

Thanks

Regards,
SG

---

quote:

---

Originally posted by manoj_niit

quote:

---

Originally posted by satheesh

Dear All,

I have a policy table and traveller table. I need to display all policy number and traveller name.

Now if there are multiple travellers in a policy it gets display in separate row.But I need to display in a single row

For eg
Policy number 1212212 has 2 travellers now I am getting output like

Policynumber FirstName Last Name
1212212 John Helen
1212212 Jermmy Wagg

Excepted result be
PolicyNumber FirstName LastName FirstName LastName
1212212 John Helen Jermmy Wagg

If there are many travellers (for eg 5)then all travellers firstname and lastname need to display in separate Column in a single row

Please any one can suggest me how to modify the below query

Select policy.policynumber,traveller.firstname, traveller.lastname
From policy
inner join traveller
On policy.policyid = traveller.policyid

Any help regarding this is highly appreciated.

Thanks

Regards,
SG

---

try This --

Create Table STUDENTS
(
SubjectID Int,
StudentName Varchar(100)
)
go
Insert into STUDENTS
Select 1,'Mary'
Union All
Select 1,'John'
Union All
Select 1,'Sam'
Union All
Select 2,'Alaina'
Union All
Select 2,'Edward'

Select Main.SubjectID,
Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID,
(Select ST1.StudentName + ',' AS [text()]
From dbo.Students ST1
Where ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
For XML PATH ('')) [Students]
From dbo.Students ST2) [Main]

---