distinct from STUFF

Question

distinct from STUFF

PSB

SSChampion

Points: 13195
More actions
January 13, 2016 at 1:15 pm

#310803

Hi,
SELECT DISTINCT t1.ProjectUID,t1.BWF , t1.Deliverable ,
--Supressed data columns
STUFF(
(SELECT ', ' + (t2.[GeoMarket])
FROM #deploys t2
where t1.ProjectUID = t2.ProjectUID AND t1.BWF = t2.BWF AND t1.Deliverable = t2.Deliverable
FOR XML PATH (''))
, 1, 1, '') AS List
INTO #Projection
FROM #deploys t1
I expected values like ABC,ACG,ADG but I am getting
ABC,ABC,ABC,ACGACG,ACG,ADG ,ADG,ADG
How do I get distinct values ?
Thanks
PSB

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Did you try putting your DISTINCT on the nested SELECT?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PSB SSChampion Points: 13195 More actions · Answer 2

Can you help me with where exactly I need to place the distinct ?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

The scope of your DISTINCT is incorrect. You're enforcing a DISTINCT set of values for the outer SELECT, but at that point the concatenated list is the value of a single column.

You need to use SELECT DISTINCT at the inner query to do what you're wanting.

Cheers!

EDIT: Just noticed Phil already made this point.

PSB SSChampion Points: 13195 More actions · Answer 4

SELECT DISTINCT t1.ProjectUID,t1.BWF , t1.Deliverable ,

--Supressed data columns

STUFF(

(SELECT ', ' + (DISTINCT t2.[GeoMarket])

FROM #deploys t2

where t1.ProjectUID = t2.ProjectUID AND t1.BWF = t2.BWF AND t1.Deliverable = t2.Deliverable

FOR XML PATH (''))

, 1, 1, '') AS List

INTO #Projection

FROM #deploys t1

I get an error with this query

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

Yes, that query is incorrect.

Use SELECT DISTINCT..., not SELECT ','+DISTINCT...

Cheers!

PSB SSChampion Points: 13195 More actions · Answer 6

Can you help me where exactly I need the distinct ?

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

Quick untested guess

😎

SELECT DISTINCT t1.ProjectUID,t1.BWF , t1.Deliverable ,

--Supressed data columns

STUFF(

(SELECT DISTINCT ', ' + (t2.[GeoMarket])

FROM #deploys t2

where t1.ProjectUID = t2.ProjectUID AND t1.BWF = t2.BWF AND t1.Deliverable = t2.Deliverable

FOR XML PATH (''))

, 1, 1, '') AS List

INTO #Projection

FROM #deploys t1

PSB SSChampion Points: 13195 More actions · Answer 8

PSB

SSChampion

Points: 13195

January 13, 2016 at 1:41 pm

#1851721

Thanks . 🙂