WITH ComponentInSeveralSolutions AS (
        SELECT SC.objectid,componenttypename,componenttype, STRING_AGG(S.uniquename, ';') AS SolutionName
        FROM solutioncomponent SC
        LEFT OUTER JOIN solution S ON SC.solutionid = S.solutionid
        WHERE 1=0
        GROUP BY SC.objectid,componenttypename,componenttype
        HAVING COUNT(*)>1)

    SELECT *,
     CASE
            WHEN ComponentType = 9 THEN (SELECT W.displayname FROM metadata.globaloptionset W WHERE W.metadataid = objectid)
            ELSE NULL
        END AS ComponentName
    FROM ComponentInSeveralSolutions
    order by Componenttype, ComponentName