我想连接两个表数据，并希望将这些数据放在一行中 [英] I want to join two tables data and want these data in a single row

问题描述

我想加入两个表数据并希望将这些数据放在一行中

例如

表格父母

Pid Pname Pemail

1 Sumit [e-mail]

2 Neeraj [e-mail]

3 Manish [e-mail]



桌子儿童

Cid Cname Pid

1 sonu 1

2 bhikhu 2

3 chintu 3

4 dhiraj 2

5 monu 2

6 monika 3



i下面使用的查询

I want to join two tables data and want these data in a single row
e.g.
Table Parent
Pid Pname Pemail
1 Sumit [e-mail]
2 Neeraj [e-mail]
3 Manish [e-mail]

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

并得到以下结果

Pid Pname Pemail Cname

2 Neeraj [e-mail] bhikhu

2 Neeraj [e-mail] dhiraj

2 Neeraj [e-mail] monu



但我想要这种格式

Pid Pname Pemail Cname1 Cname2 Cname3

2 Neeraj [e-mail] bhikhu dhiraj monu



请帮助。

and got result below
Pid Pname Pemail Cname
2 Neeraj [e-mail] bhikhu
2 Neeraj [e-mail] dhiraj
2 Neeraj [e-mail] monu

But i want it in this format
Pid Pname Pemail Cname1 Cname2 Cname3
2 Neeraj [e-mail] bhikhu dhiraj monu

Please Help.

推荐答案

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

和帮助检查此链接 [^]

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]

SQL Server PIVOT [ ^ ]应该帮助你。

SQL Server PIVOT[^] should help you out.

这篇关于我想连接两个表数据，并希望将这些数据放在一行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！