A database of thesauruses where terms and categories are related to each other and run SQL Server 2008. Based on this and this answers. Here is an example:
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
SELECT
CASE
WHEN
THEN
ELSE NULL
END,
CHAR(9) + 'SN ' +
CHAR(9) +
CHAR(9) + 'CODE ' +
FROM
INNER JOIN
INNER JOIN
LEFT JOIN
GROUP BY GROUPING SETS (
ORDER BY
GO
DROP TABLE
DROP TABLE
DROP TABLE
GO
If there is NULL in "enccope", I have a repeating string.
If there is no categorylink category value, I have a repeating line.
How to avoid this, please?
I want to COALESCE them all in one column without duplicates.
; WITH CTEterm AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY
CHAR(9) + 'SN ' +
CHAR(9) +
CHAR(9) + 'CODE ' +
FROM
INNER JOIN
INNER JOIN
LEFT JOIN
)
SELECT COALESCE(
CASE
WHEN rownumber = 1
THEN mainterm
ELSE NULL
END,
scopenote,
subterms,
codes
)
FROM CTEterm
GROUP BY GROUPING SETS ((mainterm, rownumber), (mainterm, scopenote), (mainterm, subterms), (mainterm, codes))
ORDER BY mainterm, codes, subterms, scopenote
GO
Basically, how to avoid using "ELSE NULL" in CASE (for example, "else skip row")?
Here is what I use COALESCE
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
This is what I need
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
Same question here