select distinct 起点,终点 into #tbl from 线路表 a where isnull((select count(1) from 线路表 b where b.id>a.id and
b.起点=a.终点 and b.终点=a.起点),0)<2
--select * from #tbl
select [ID] = identity(bigint,1,1),
起点,终点,线路=REPLICATE(' ',255)
into #tbl2
from #tbl
DECLARE @_Line NVARCHAR(255)
DECLARE @_i BIGINT
SET @_i = 1
DECLARE @_j BIGINT
SET @_j = 1
while @_i<=(SELECT COUNT(1) FROM #tbl2)
BEGIN
SET @_j = 1
SET @_Line = ''
WHILE @_j <=(SELECT COUNT(1) FROM 线路表)
BEGIN
SET @_Line = @_Line + ISNULL(( SELECT CASE @_Line
WHEN '' THEN ''
ELSE ','
END
+线路 FROM 线路表
WHERE [ID] = @_j AND (
(
线路表.起点 = (SELECT 起点 FROM #tbl2 WHERE [ID]=@_i)
AND 线路表.终点 = (SELECT 终点 FROM #tbl2 WHERE [ID]=@_i)
)
OR
(
线路表.起点 = (SELECT 终点 FROM #tbl2 WHERE [ID]=@_i)
AND 线路表.终点 = (SELECT 起点 FROM #tbl2 WHERE [ID]=@_i)
)
)
),'')
print @_Line
SET @_j = @_j + 1
END
UPDATE #tbl2 SET 线路 = @_Line WHERE [ID]=@_i
SET @_i=@_i+1
END
SELECT * FROM #tbl2
drop table #tbl
drop table #tbl2
SELECT a.起点, a.终点, a.线路 = a.线路 + ',' +
(SELECT b.线路
FROM 线路表 b
WHERE b.起点= a.终点 AND b.终点 = a.起点 and b.id> a.id)
FROM 线路表 AS a
WHERE isnull(a.线路 + ',' +
(SELECT b.线路
FROM 线路表 b
WHERE b.起点= a.终点 AND b.终点 = a.起点 and b.id> a.id));
SELECT 起点, 终点, 线路 =线路+ ',' +
(SELECT 线路
FROM 线路表 b
WHERE b.起点= a.终点 AND b.终点 = a.起点 and b.id> a.id)
FROM 线路表 a
where isnull(线路+ ',' +
(SELECT 线路
FROM 线路表 b
WHERE b.起点= a.终点 AND b.终点 = a.起点 and b.id> a.id),'')<>''