How To Connect Lines In Single Feature In Sql Server?

I have a some tables in SQL Server 2008 R2. I want to create view to publish it on Geoserver. But I have a problem with geometry. I have a geometry of segments of line. For example

Solution 1:

STUnion is indeed your road (ha!) to salvation. STUnion is a method on the geometry and geography CLR types. You'd typically invoke it as gInstance.STUnion(othergInstance). That said, assuming that your query above generates the appropriate geometry instances, this recursive CTE solution should work:

with by_segment as (
SELECT  
    Road.Road_Id,
    GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom,
    row_number() over (partition by Road.road_id order by Road.Segment_Id) as [rn],
    count(*) over (partition by Road.road_id) as [c]
FROM dbo.Road
LEFT JOIN Segment_ID  ON Road.Road_ID = Segment_ID.Road_ID
LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID
),
roads_by_segment as (
select 
   road_id,
   the_geom,
   [rn],
   [c]
from by_segment
where [rn] = 1

union all

select 
   [a].road_id,
   [a].the_geom.STUnion([b].the_geom),
   [b].[rn],
   [b].[c]
from by_segment as [a]
inner join roads_by_segment as [b]
   on [a].Road_ID = b.Road_ID
   and [a].[rn] = [b].[rn]+1
)

select * from roads_by_segment where [rn] = [c]