请教一个查重相关的SQL语句..

[abli777]

字段如下:

身份证 本人手机 联系人1 联系人2 联系人3

求实现以下效果：

本人手机/联系人1/联系人2/联系人3 这4个字段任意两两间的交叉人数(统计交叉了多少人）、展示所交叉人的id（比如交叉了2个人，展示这2个人的id）。

注：如果A的‘联系人1’和B的‘本人手机’交叉，A的‘联系人3’和B的‘联系人2’交叉，则A、B的交叉人数都是1而不是2.

[二月十六]

建议楼主给出测试数据和对应的结果，比如 身份证 本人手机 联系人1 联系人2 联系人3 1 张三 13 14 15 想要的结果是 1 1

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	cardNo    VARCHAR(20) PRIMARY KEY,  
	c0  VARCHAR(20),
	c1   VARCHAR(20),
	c2   VARCHAR(20),
	c3	  VARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES ('1X','1','2','3','4')
INSERT INTO t VALUES ('2X','2','2','2','2')
INSERT INTO t VALUES ('3X','1','1','3','4')
INSERT INTO t VALUES ('4X','1','1','1','1')

;WITH cte AS (
	SELECT cardNo,c0 AS cc FROM t
	UNION
	SELECT cardNo,c1 FROM t
	UNION
	SELECT cardNo,c2 FROM t
	UNION
	SELECT cardNo,c3 FROM t
)
,cte2 AS (
SELECT 
a.cardNo
,tt.cardNoFlag
FROM cte AS a CROSS APPLY (
	SELECT b.cardNo AS cardNoFlag FROM cte AS b WHERE a.cardNo!=b.cardNo AND a.cc=b.cc	
) AS tt
)
SELECT a.cardNo
,(SELECT count(DISTINCT b.cardNoFlag) FROM cte2 AS b WHERE a.cardNo=b.cardNo ) AS cnt
,STUFF((SELECT DISTINCT ','+ b.cardNoFlag FROM cte2 AS b WHERE a.cardNo=b.cardNo FOR XML PATH('')),1,1,'') AS otherCardNo 
FROM t AS a
GROUP BY a.cardNo

/*
cardNo	cnt	otherCardNo
1X	        3	2X,3X,4X
2X	        1	1X
3X	        2	1X,4X
4X	        2	1X,3X
*/