CREATE TABLE #term (termid VARCHAR(8), en VARCHAR(32), enscope VARCHAR(32))
CREATE TABLE #link (linkid VARCHAR(10), termid VARCHAR(8), reltype VARCHAR(2), refid VARCHAR(8))
CREATE TABLE #categorylink (code VARCHAR(3), termid VARCHAR(8))

INSERT INTO #term VALUES ('100', 'ABC', 'abc_scopenote')
INSERT INTO #term VALUES ('120', 'DEF', 'def_scopenote')
INSERT INTO #term VALUES ('150', 'GHI', NULL)

INSERT INTO #link VALUES ('1', '100', 'NT', '120')
INSERT INTO #link VALUES ('2', '100', 'NT', '150')
INSERT INTO #link VALUES ('3', '120', 'BT', '100')
INSERT INTO #link VALUES ('4', '120', 'RT', '150')
INSERT INTO #link VALUES ('5', '150', 'BT', '100')
INSERT INTO #link VALUES ('6', '150', 'RT', '120')

INSERT INTO #categorylink VALUES ('S01', '100')
INSERT INTO #categorylink VALUES ('S02', '100')
INSERT INTO #categorylink VALUES ('B04', '150')

SELECT
       CASE
       WHEN #term.enscope IS NULL AND refterm.en IS NULL AND #categorylink.code IS NULL
       THEN #term.en
       ELSE NULL
       END,
       CHAR(9) + 'SN ' + #term.enscope,
       CHAR(9) + #link.reltype + CHAR(32) + refterm.en,
       CHAR(9) + 'CODE ' + #categorylink.code
    FROM #link
        INNER JOIN #term ON #term.termid = #link.termid
        INNER JOIN #term AS refterm ON refterm.termid = #link.refid
        LEFT JOIN #categorylink ON #term.termid = #categorylink.termid

    GROUP BY GROUPING SETS (#term.en, (#term.en, #term.enscope), (#term.en, #link.linkid, #link.reltype, refterm.en), (#term.en, #categorylink.code))
    ORDER BY #term.en, #categorylink.code, #link.linkid, #term.enscope
GO

DROP TABLE #term
DROP TABLE #link
DROP TABLE #categorylink
GO

; WITH CTEterm AS (
	SELECT
	   ROW_NUMBER() OVER (PARTITION BY #term.en, #term.enscope, refterm.en, #categorylink.termid ORDER BY #term.en) AS rownumber,
       #term.en AS mainterm,
       CHAR(9) + 'SN ' + #term.enscope AS scopenote,
       CHAR(9) + #link.reltype + CHAR(32) + refterm.en AS subterms,
       CHAR(9) + 'CODE ' + #categorylink.code AS codes
    FROM #link
       INNER JOIN #term ON #term.termid = #link.termid
       INNER JOIN #term AS refterm ON refterm.termid = #link.refid
       LEFT JOIN #categorylink ON #term.termid = #categorylink.termid
)

SELECT
    CASE
    WHEN rownumber = 1
    THEN mainterm
    ELSE NULL
    END,
    
    scopenote,
    subterms,
    codes
	
FROM CTEterm
GROUP BY GROUPING SETS ((rownumber, mainterm), (mainterm, scopenote), (mainterm, subterms), (mainterm, codes))
ORDER BY mainterm, codes, subterms, scopenote

GO

ABC
NULL
    SN abc_scopenote
    NT DEF
    NT GHI
    CODE S01
    CODE S02
NULL
DEF
    SN def_scopenote
    BT ABC
    RT GHI
NULL
GHI
    BT ABC
    RT DEF
    CODE B04

ABC
    SN abc_scopenote
    NT DEF
    NT GHI
    CODE S01
    CODE S02
DEF
    SN def_scopenote
    BT ABC
    RT GHI
GHI
    BT ABC
    RT DEF
    CODE B04