Split lat long from different rows

[DarinLevesque]

I have data that is arranged like below:

id	value	path
194562	-94.58462	1
194562	34.452312	1
194562	-94.26451	2
194562	32.561232	2
198723	-93.894564	3
198723	33.769423	3

The Value field has a longitude and a latitude for each path number and they are related to various project numbers (the id's). 

I've tried to create a variable and dynamically create a field alias based upon whether the row had a longitude (starting with a - sign, or a latitude, but that didn't appear to do the trick (see below):

Let LatLong = if(Left(@Value,1)='-', 'long',if(Left(@Value,1)<>'-', 'lat'));

temp:
left Join(attributes) load fc_system_id,
	__KEY_paths_u0,
    if(Left(@Value,1)='-', [@Value],if(Left(@Value,1)<>'-', [@Value])) as $(LatLong)
Resident attributes;

I also tried a generic load, but with such a large data set that didn't makes sense.

Am I missing an easier way to go about this? ideally I'd like one row per path number but I'm stumping myself on this one.

[Kushal_Chawda · Accepted solution]

@DarinLevesque  try below

Data:
LOAD Path,
     ID,
     subfield(Lat_Long,';') as Long,
     subfield(Lat_Long,';',2) as Lat;
LOAD Path,
     concat(distinct ID) as ID,
     Concat (Value,';',Value) as Lat_Long
FROM Table
group by Path;

[Kushal_Chawda]

@DarinLevesque  updated previous reply