T-SQL SELECT concatenates multiple rows into a single column

This is a SQL Server 2008 thesaurus database where terms and categories are related. Hierarchical data. Just 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 links) by category:

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

The 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 this to look in one 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

UNPIVOT does not seem to fit. Maybe CTE + COALESCE?

Is there a way to convert rows to a single column according to the pattern:

term.en
<tab> SN term.enscope <if NOT NULL>
<tab> link.reltype term.en
 ...
<tab> CODE category.code
+1
source share
1 answer

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

+2
source

All Articles