/*Question #1*/
WITH Cte AS
(
      SELECT DISTINCT sno, pno_list
        FROM SupParts SP
          CROSS APPLY
          (SELECT ',' + pno
             FROM SupParts
            WHERE sno = SP.sno
            ORDER BY sno
              FOR XML PATH('')) AS D(pno_list)
)
SELECT A.sno, B.sno
  FROM Cte AS A,
       Cte AS B
 WHERE A.pno_list = B.pno_list
   AND A.sno < B.sno; 

/*Question #2*/
WITH Cte AS
(
      SELECT DISTINCT sno, pno_list
        FROM SupParts SP
          CROSS APPLY
          (SELECT ',' + pno
             FROM SupParts
            WHERE sno = SP.sno
            ORDER BY sno
              FOR XML PATH('')) AS D(pno_list)
)
SELECT DISTINCT STUFF(B.sno_list, 1, 1, ''),
                STUFF(A.pno_list, 1, 1, '')
  FROM Cte A
    CROSS APPLY
    (SELECT ',' + sno
       FROM Cte
      WHERE pno_list = A.pno_list
      ORDER BY sno
        FOR XML PATH('')) B(sno_list); 

/*Question #3*/
SELECT DISTINCT SP.sno, D.pno_list
  FROM SupParts SP
    CROSS APPLY
    (SELECT '$' + pno + '~' + CAST(COUNT(*) AS VARCHAR(10))
       FROM SupParts
      WHERE sno = SP.sno
      GROUP BY pno
      ORDER BY pno     
        FOR XML PATH('')) D(pno_list)
    CROSS APPLY
    (SELECT '$' + pno + '~' + CAST(cnt AS VARCHAR(10))
       FROM Goal
      ORDER BY pno
        FOR XML PATH('')) T(pno_list)
  WHERE D.pno_list = T.pno_list;