13,254,187 members (64,498 online)
Rate this:
See more:
I want to join two tables data and want these data in a single row
e.g.
Table Parent
Pid Pname Pemail
1 Sumit sy@ymail.com
2 Neeraj nl@gmail.com
3 Manish m@gmail.com

Table Child
Cid Cname Pid
1 sonu 1
2 bhikhu 2
3 chintu 3
4 dhiraj 2
5 monu 2
6 monika 3

i used query below
```select parent.Pid,parent.Pname,parent.Pemail,child.Cname
from parent right outer join child
ON parent.Pid= child.Pid
where parent.Pid=2```

and got result below
Pid Pname Pemail Cname
2 Neeraj nl@gmail.com bhikhu
2 Neeraj nl@gmail.com dhiraj
2 Neeraj nl@gmail.com monu

But i want it in this format
Pid Pname Pemail Cname1 Cname2 Cname3
2 Neeraj nl@gmail.com bhikhu dhiraj monu

Posted 9-Jan-13 4:50am
roxyraj860
Updated 9-Jan-13 6:01am
v2
willempipi 9-Jan-13 11:14am

I'm really curious about the correct answer to this question. My solution (if it would be required to be in SQL) would be to first query the maximum amount of children per parent, than create a temporary table with the amount of columns, than use a cursor to fill the temporary table.

Rate this:

## Solution 3

```select *
from
(
select val, Cid,Item
from child
unpivot
(
val
for Item in (Cname)
)u
) x
pivot
(
max(val)
for Cid in ([1], [2], [3], [4], [5], [6],[7], [8], [9], [10], [11], [12],[13])
) p```

and for Help check this link [^]
v3
Rate this:

## Solution 1

```Select Parent.Pid,Parent.Pname,Parent.Pemail,
Left(Main.Child,Len(Main.Child)-1) As "ChildValues"
From(Select distinct Child2.SubjectID,
(Select Child1.Cname AS [text()]
From dbo.Child Child1
Where Child1.Pid = Child2.Pid
ORDER BY Child1.Pid
For XML PATH ('')) [Child]
From dbo.Child Child2) [Main]```
Rate this:

## Solution 2

SQL Server `PIVOT`[^] should help you out.

Top Experts
Last 24hrsThis month
 Richard Deeming 160 ppolymorphe 140 GobblesGobbles 128 Karthik Bangalore 120 Dave Kreskowiak 110
 OriginalGriff 3,934 Karthik Bangalore 2,271 ppolymorphe 1,669 Dave Kreskowiak 1,411 CPallini 1,240