Split a string in and use the resulted array to populate into two separate columns

One way of doing this would be some string manipulation to convert the string into an XML element with attributes named GID and PACKAGEID and then use XML methods on that

SELECT PIDs,
       GID = X.value('x[1]/@GID', 'VARCHAR(100)'),
       PACKAGEID = X.value('x[1]/@PACKAGEID', 'VARCHAR(100)')
FROM Data
CROSS APPLY (VALUES (CONVERT(XML,
                '<x ' + REPLACE(REPLACE(PIDs, ': ', '="'),',','" ') + '" />'
            ))) V(X)

Or much the same approach but using JSON instead (which gives a simpler execution plan than the XML one)

SELECT PIDs, GID, PACKAGEID
FROM Data
CROSS APPLY 
     OPENJSON('{"' + REPLACE(REPLACE(PIDs, ': ', '":"'), ', ', '","') + '"}')
WITH 
     (GID VARCHAR(100), PACKAGEID VARCHAR(100))

Fiddle

You can accomplish this by using a combination of CHARINDEX(), LEFT(), and either RIGHT() or STUFF() to manually split the strings in two steps. Serialize the first split strings into a CROSS APPLY(VALUES ...) that feeds the second split. After the second split, the resulting name/value combinations cam be reorganized using conditional aggregation to get your final results.

SELECT *
FROM Data D
CROSS APPLY (
    SELECT
        MAX(CASE WHEN NV.Name = 'GID' THEN NV.Value END) AS GID,
        MAX(CASE WHEN NV.Name = 'PACKAGEID' THEN NV.Value END) AS PACKAGEID
    FROM (SELECT CHARINDEX(',', D.PIDs) AS Pos) P
    CROSS APPLY (
        VALUES
            (LEFT(D.PIDs, P.Pos - 1)),
            (STUFF(D.PIDs, 1, P.Pos, ''))
    ) I(Item)
    CROSS APPLY (SELECT CHARINDEX(':', I.Item) AS Pos2) P2
    CROSS APPLY (
        SELECT
            TRIM(LEFT(I.Item, P2.Pos2 - 1)) AS Name,
            TRIM(STUFF(I.Item, 1, P2.Pos2, '')) AS Value
    ) NV
) A

If using SQL Server 2022 or later, you can use the STRING_SPLIT() function to accomplish the same. (Note that the second STRING_SPLIT() below requires use of the enable_ordinal option, not available prior to 2022.)

SELECT *
FROM Data D
CROSS APPLY (
    SELECT
        MAX(CASE WHEN NV.Name = 'GID' THEN NV.Value END) AS GID,
        MAX(CASE WHEN NV.Name = 'PACKAGEID' THEN NV.Value END) AS PACKAGEID
    FROM STRING_SPLIT(D.PIDs, ',') S
    CROSS APPLY (
        SELECT
            TRIM(MAX(CASE WHEN S2.ordinal = 1 THEN S2.value END)) AS Name,
            TRIM(MAX(CASE WHEN S2.ordinal = 2 THEN S2.value END)) AS Value
        FROM STRING_SPLIT(S.value, ':', 1) S2
    ) NV
) A

For versions older than 2017, you will need to replace the TRIM() function with LTRIM() or an LTRIM(RTRIM()) combo.

Results:

See this db<>fiddle for a demo.

You can convert PIDs column to JSON and then take JSON_VALUE.

See example

select 
  json_value(jPIDs,'$.GID') GID
 ,json_value(jPIDs,'$.PACKAGEID') PACKAGEID
  ,*
from(
  select *
    ,replace('{"'+replace(replace(pids,':','":"'),',','","')+'"}',' ','') jPIDs
  from test
)a

Demo

This can also be done using text functions availabale in SQL Server. You can achived the first expected output like this:

with flo as(
SELECT PIDS, SUBSTRING(PIDS,1 ,CHARINDEX(',',PIDS)-1)AS left_side, SUBSTRING(PIDS, CHARINDEX(',',PIDS)+2,255)AS right_side
FROM Data 
), flo1 as (
select case when left(left_side,1)<left(right_side,1) then left_side else right_side end as GID, 
case when left(left_side,1)>left(right_side,1) then left_side else right_side end as Packageid
from flo
)
select substring(gid, charindex(':',gid)+2,255)as gid,
substring(packageid, charindex(':',packageid)+2,255)as packageid
from flo1

And the second expected output like this:

with flo as(
SELECT PIDS, SUBSTRING(PIDS,1 ,CHARINDEX(',',PIDS)-1)AS left_side, SUBSTRING(PIDS, CHARINDEX(',',PIDS)+2,255)AS right_side
FROM Data 
)
select case when left(left_side,1)<left(right_side,1) then left_side else right_side end as GID,
 case when left(left_side,1)>left(right_side,1) then left_side else right_side end as Packageid
from flo

Hope it helps.