T-SQL SELECT combine multiple rows into one column

问题:

This is thesaurus database running SQL Server 2008 where terms and categories linked to each other. Hierarchical data. Simply three tables:

CREATE TABLE term (
    termid      INT NOT NULL PRIMARY KEY,
    en          VARCHAR(95) NOT NULL,
    enscope     VARCHAR(640),
    dscr        BIT NOT NULL
)

CREATE TABLE link (
    id          INT NOT NULL IDENTITY PRIMARY KEY,
    termid      INT NOT NULL FOREIGN KEY REFERENCES term(termid),
    reltype     CHAR(3) NOT NULL,
    refid       INT NOT NULL FOREIGN KEY REFERENCES term(termid)
)

CREATE TABLE categorylink (
    id         INT NOT NULL IDENTITY PRIMARY KEY,
    code       CHAR(5) NOT NULL,
    termid     INT NOT NULL FOREIGN KEY REFERENCES term(termid)
)

Now extract some descriptors (terms with references) by category codes:

SELECT Term = term.en,
       [Scope note] = CHAR(9) + 'SN ' + term.enscope,
       [Ref. terms] = CHAR(9) + link.reltype + CHAR(32) + term1.en,
       Categories = CHAR(9) + 'CODE ' + categorylink.code
    FROM link
    LEFT JOIN term ON term.termid = link.termid
    LEFT JOIN term AS term1 ON term1.termid = link.refid
    LEFT JOIN categorylink ON categorylink.termid = term.termid
    WHERE term.termid IN (SELECT termid FROM categorylink WHERE code IN ('S07', 'S08'))
    GROUP BY term.en, term1.en, link.id, link.reltype, term.enscope, categorylink.code
    ORDER BY term.en, link.id

Result is quite a mess:

Term            Scope note   Ref. terms                      Categories
ACPR REACTOR    SN New M...  UF  acrr reactor                CODE S07  
ACPR REACTOR    SN New M...  UF  annular core pulse reactor  CODE S07  
ACPR REACTOR    SN New M...  BT  ENRICHED URANIUM REACTORS   CODE S07  
ACPR REACTOR    SN New M...  BT  HYDRIDE MODERATED REACTORS  CODE S07  
ACPR REACTOR    SN New M...  BT  MIXED SPECTRUM REACTORS     CODE S07  
ACPR REACTOR    SN New M...  BT  PULSED REACTORS             CODE S07  
ACPR REACTOR    SN New M...  BT  RESEARCH REACTORS           CODE S07  
ACPR REACTOR    SN New M...  BT  SOLID HOMOGENEOUS REACTORS  CODE S07  
ACPR REACTOR    SN New M...  BT  WATER COOLED REACTORS       CODE S07  
ACPR REACTOR    SN New M...  BT  WATER MODERATED REACTORS    CODE S07  
ACTINIUM 225    NULL         BT  ACTINIDE NUCLEI             CODE C6400
ACTINIUM 225    NULL         BT  ACTINIDE NUCLEI             CODE S07  
ACTINIUM 225    NULL         BT  ACTINIDE NUCLEI             CODE S62

etc, etc...

I want it to look like this in a single output column:

ACPR REACTOR
  SN New Mexico, USA. Shut down in 1977
  UF acrr reactor
  UF annular core pulse reactor
  BT ENRICHED URANIUM REACTORS
  BT HYDRIDE MODERATED REACTORS
  BT MIXED SPECTRUM REACTORS
  BT PULSED REACTORS
  BT RESEARCH REACTORS
  BT SOLID HOMOGENEOUS REACTORS
  BT WATER COOLED REACTORS
  BT WATER MODERATED REACTORS
  CODE S07
ACTINIUM 225
  BT ACTINIDE NUCLEI
  CODE C6400
  CODE S07
  CODE S62

Seems UNPIVOT doesn't fit. Maybe CTE + COALESCE?

Is there any way to transform rows into one column according to pattern:

term.en
<tab> SN term.enscope <if NOT NULL>
<tab> link.reltype term.en
 ...
<tab> CODE category.code

回答1:

I believe your question is identical to this one. You will find your answer there.