我在一个联系人栏中有多个电话号码、多个电子邮件地址、多个传真地址。我需要一个查询来获得如下结果
Contact Id |Phone |Email |Fax
1 |Phone1, Phone2, Phone3|Email1, Email2,Email3,Email4|Fax1, Fax2
2 |Phone1, Phone2 |Email1, Email2,Email3 |Fax1, Fax2
我的输入就像
Value MeansOfCommunicationDescription ContactId
[email protected] Email 2
www.1_port2.com Web Site 2
Test Insert Fax 2
Test Insert Web Site 2
Test Insert Web Site 2
Test Insert Web Site 2
Test Insert Web Site 2
Test Insert Web Site 2
Test Insert Web Site 2
test Phone 2
test Phone 2
test Phone 2
Test Insert Web Site 2
Test Insert Email 2
您可以使用CTE来获得您想要的结果。您可以尝试以下查询:
;WITH ctePhone
AS (
SELECT DISTINCT ContactId [ID],
STUFF(( SELECT N', ' + Phone
FROM Table1 B
WHERE B.ContactId = A.ContactId
FOR XML PATH(''), TYPE
).value('(.)', 'NVARCHAR(MAX)'), 1, 1, '') [Phone],
STUFF(( SELECT N', ' + Email
FROM Table1 B
WHERE B.ContactId = A.ContactId
FOR XML PATH(''), TYPE
).value('(.)', 'NVARCHAR(MAX)'), 1, 1, '') [Email],
STUFF(( SELECT N', ' + Fax
FROM Table1 B
WHERE B.ContactId = A.ContactId
FOR XML PATH(''), TYPE
).value('(.)', 'NVARCHAR(MAX)'), 1, 1, '') [Fax]
FROM Table1 A
)
SELECT DISTINCT #Table1.ContactId,
ctePhone.Phone,
ctePhone.Email,
ctePhone.Fax
FROM Table1
INNER JOIN ctePhone
ON Table1.ContactId = ctePhone.[ID]
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句