Using distinct within for xml path without including in select

Using distinct within for xml path without including in select

The top query looks in a table that will only ever have one record per
statement, so if a customer has 7 statements the number of rows should be
7 and the query would list them like so 1,2,3,4,5,6,7
The bottom query looks in a table which will have the 7 statements like
above, but more often than not, they will be split, so if there is 2 lines
for each statement there would be 14, i.e. 1,1,2,2,3,3,4,4,5,5,6,6,7,7
Now, what I'm trying to achieve is the following, the top query is fine as
it is, however the bottom query needs to be made distinct. Look at result
number 2 on the bottom image, I want this to come back as
1,2,3,4,5,6,7,8,9,10.
If there was a particular instance where the statements went up to 8 and 4
respectively, i would want 1,2,3,4,5,6,7,8. Basically It is in most cases
duplicating the statement numbers for the bottom query and I would like it
to just do a distinct occross the whole result, however I have tried
putting a distinct in and it complains that order by items must be
contained in the select statement if contained within a distinct, this
ruins my query.
The ultimate aim of these two queries is to compare the top result set
against the bottom and bring back only the ones that dont match (as that
will mean I am missing a statement in the top queries table)
--Shows the each consolidated statement number that exisits for that
particular customer reference number within the dbo.rss table.
Select Main.cust_ref,
Left(CAST(Main.consolidatedstatements as
varchar(max)),Len(CAST(Main.consolidatedstatements as
varchar(max)))-1) As "consolidatedstatements"
From(Select distinct ST2.cust_ref,
(Select CAST(ST1.consolidated_stmt_num as varchar(max)) + ','
AS [text()]
From dbo.rss ST1
Where ST1.cust_ref = ST2.cust_ref
ORDER BY ST1.cust_ref
For XML PATH ('')) [consolidatedstatements]
From dbo.rss ST2) [Main]
--Shows the each consolidated statement number that exisits for that
particular customer reference number within the dbo.SC table.
Select Main.cust_ref,
Left(CAST(Main.consolidatedstatements as
varchar(max)),Len(CAST(Main.consolidatedstatements as
varchar(max)))-1) As "consolidatedstatements"
From(Select distinct ST2.cust_ref,
(Select CAST(ST1.consolidated_stmt_num as varchar(max)) + ','
AS [text()]
From dbo.SC ST1
Where ST1.cust_ref = ST2.cust_ref
ORDER BY ST1.cust_ref
For XML PATH ('')) [consolidatedstatements]
From dbo.SC ST2) [Main]