Following query will give the required result for you.
SELECT
Fid,
(SELECT
top 1
STUFF(
(SELECT
'-' + T4.Orgin
FROM
dbo.fnSplitString( ot.SegId ,',') t3
join table2 t4 on t3.splitdata = t4.segid FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
+ '-' + t2.Destination
FROM
dbo.fnSplitString( ot.SegId ,',') t1 join table2 t2 on t1.splitdata = t2.segid order by t1.splitdata desc)
AS Segments,
Amount,*
FROM TABLE1 ot
In the above query fnSplitString() user defined function has been used the code for this function is
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
Taken from
How to Split a string by delimited char in SQL Server.............. - SQLServerCentral[
^]
Good luck! But try to change the table structure if possible :)