SQL to build point arrays

[sunsilk10]

Hi I have a link and a node SQL Server table as below.

I need to build a number of lines using the node and link tables. Non-vertex nodes denote end points of
the line.

In C# I possibly need build a dictionary of coordinates for each line.
Any suggestions what would be the best way to achieve this.​

NodeID Coords Type
A x1,y1 Valve
B x2,y2 Valve
C x3,y3 Valve
D x4,y4 Vertex
E x5,y5 Valve

Fromnode Tonode
A B
B C
C D
D E

Expected Output
Line 1 coordinates: (x1,y1), (x2,y2)
Line 2 coordinates: (x2,y2), (x3,y3)
Line 3 coordinates: (x3,y3), (x4,y4), (x5,y5)​

 

[jmcilhinney]

What's the data type of that Coords column?

 

[sunsilk10]

The cords is actually in 2 separate fields X and Y - data type integer.
The issue is being able to process vertices. I have tried an sql statement as below, but this doesn't work

It returns the following records for FROMNODEID = 'BEND3'

BEND3 VERTEX1 INSERT (439720.06000000,350268.73000000),(439730.20000000,350286.62000000) NULL HPPE
VERTEX1 VERTEX2 INSERT (439730.20000000,350286.62000000),(439769.96000000,350301.80000000) NULL HPPE
VERTEX2 NFH2 INSERT (439769.96000000,350301.80000000),(439802.78000000,350313.47000000) 41 HPPE

Instead I expected to get 1 record:
BEND3 NFH2 INSERT (439720.06000000,350268.73000000),(439730.20000000,350286.62000000), (439769.96000000,350301.80000000), (439802.78000000,350313.47000000) NULL HPPE



select conn.FROMNODEID, conn.TONODEID, conn.ACTION,
'('+ CAST(c1.XCOORD as VARCHAR)+','+ CAST(c1.YCOORD as VARCHAR)+'),
('+CAST(c2.XCOORD as VARCHAR)+','+ CAST(c2.YCOORD as VARCHAR)+')'as
Coords,
c2.SUBTYPE, conn.MATERIAL
from SURVEYLINES conn
leftouterjoin SURVEYPOINTS c1 on conn.FROMNODEID = c1.NodeID
leftouter joinSURVEYPOINTS c2 on conn.TONODEID = c2.NodeID