SQL语句，将姓名相同的不同数据显示成一行(0分)

L

liuguilg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-10-26

• #1

如：原表记录数据
Name PhoneNo 　　HomeNo 　　 OfficeNo
张三　1390826858
张三　　　　　　　85698545
张三　　　　　　　　　　　　　　88985831

现想显示成一行：
Name PhoneNo 　　HomeNo 　　 OfficeNo
张三　1390826858　85698545　　88985831

 

L

liuguilg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-10-26

• #2

Select T1.Name, T1.PhoneNo, T2.HomeNo, T3.OfficeNo
From 原表 T1
Inner Join 原表 T2 On T1.Name=T2.Name
Inner Join 原表 T3 On T1.Name=T3.Name
Where (T1.PhoneNo IS NOT NULL)
AND (T2.HomeNo IS NOT NULL)
AND (T3.OfficeNo IS NOT NULL)

 

G

googledcjt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-10-26

• #3

学习中。。。

 

X

xinjinren

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-10-27

• #4

最好是T2为 select name,homeno from 原表 where rtrim(isnull(homeno,''))<>''
T3为 select name,OfficeNo from 原表 where rtrim(isnull(OfficeNo,''))<>''

 

C

czcn

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-10-27

• #5

select distinct name,
(select top 1 phoneno from T where name = a.name and not phoneno is null) as phoneno,
(select top 1 homeno from T where name = a.name and not homeno is null) as homeno,
(select top 1 officeno from T where name = a.name and not officeno is null) as officeno
from T a