T-sql Coalesce Grouping Sets Into Single Column Without Null Duplicates

A thesaurus database where terms and categories are linked to each other and running SQL Server 2008. Based on this and this answers. Here is a sample: CREATE TABLE #term (termid V

Solution 1:

Sorry if this turns out not really what you expected, but if you simply need to get rid of NULLs then I fail to see why you can't just do like this:

;WITH CTEterm AS (
    SELECT
       ROW_NUMBER() OVER (PARTITION BY #term.en, refterm.en
                              ORDERBY #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 codesFROM #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
  AggValue
FROM (
  SELECT
    mainterm, codes, subterms, scopenote,
    COALESCE(
      CASEWHEN rownumber = 1THEN mainterm ELSE NULL END,
      scopenote,
      subterms,
      codes
    ) AS AggValue
  FROM CTEterm
  GROUPBY GROUPING SETS ((mainterm, rownumber), (mainterm, scopenote),
                          (mainterm, subterms), (mainterm, codes))
) s
WHERE AggValue ISNOT NULL
ORDERBY mainterm, codes, subterms, scopenote

Note: ELSE NULL is removed here only because it changes nothing (NULL is implied when there's no ELSE), not because you would gain anything from removing it.