Trying to get consecutive distinct from the following XML.
<x> AB </x> <x> AB </x> <x> AB </x> <x> AC </x> <x> AB </x>
Expected Result :
AB AC AB
Current Result :
AB AC
Code :
SELECT * ,STUFF(( TheParts.query(' for $x in distinct-values(/x/text()) return <x>{concat(" | ", $x)}</x> ').value('.', 'varchar(max)') ), 1, 1, '') AS Hierarchy FROM Table
The distinct-values get distinct from the whole set, but I would like to get distinct values only if there are consecutive.
Advertisement
Answer
We have a pure XQuery solution.
SQL
SELECT REPLACE(CAST('<x>AB</x> <x>AB</x> <x>AB</x> <x>AC</x> <x>AB</x>' as xml) .query(' for $v in x let $n := x[. >> $v][1] return if ($v/text()[1] = $n/text()[1]) then () else data($v) ').value('.', 'VARCHAR(MAX)'), SPACE(1), ' | ') AS Result;
Output
+--------------+ | Result | +--------------+ | AB | AC | AB | +--------------+