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