Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
 
Please Help.
Posted 9-Jan-13 3:50am
roxyraj686
Edited 9-Jan-13 5:01am
v2
Comments
willempipi at 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: bad
good
Please Sign up or sign in to vote.

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 [^]
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

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]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 686
1 OriginalGriff 345
2 CPallini 200
3 PIEBALDconsult 150
4 Magic Wonder 131
0 OriginalGriff 5,795
1 Sergey Alexandrovich Kryukov 5,028
2 CPallini 4,700
3 George Jonsson 3,142
4 Gihan Liyanage 2,450


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 13 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100