CODEWITHSUNDEEP
sqlsql-servert-sql
Jext
Jext

Reputation: 75

Show unique values only when using CTE - SQL Server

I have the following query:

with GTS_cte AS
    (SELECT distinct [BusinessTermID], GTS_T = 
    STUFF                   ((SELECT ', ' + dbo.TblField.GTS_table
                             FROM      dbo.TblField
                             WHERE   [BusinessTermID] = Y.[BusinessTermID] AND dbo.TblField.GTS_table <> '' FOR XML PATH('')), 1, 2, '')
     FROM      dbo.Tblfield AS Y
     GROUP BY [BusinessTermID])

    ,
  syn_cte as (
SELECT [BusinessTermID], syns = STUFF
                      ((SELECT ', ' + dbo.TblBusinessSynonym.Synonym
                        FROM      dbo.TblBusinessSynonym
                        WHERE   [BusinessTermID] = x.[BusinessTermID] AND dbo.TblBusinessSynonym.Synonym <> '' FOR XML PATH('')), 1, 2, '')
FROM     dbo.TblBusinessSynonym AS x
GROUP BY [BusinessTermID])


select syn_cte.BusinessTermID, syn_cte.syns, GTS_cte.GTS_T 
from  syn_cte  join 
  GTS_cte on GTS_cte.BusinessTermID = syn_cte.BusinessTermID

It is concatenating the fields correctly and linking them but it is now creating duplicates. My result set looks like this:

enter image description here

Is there a way to show only Unique values in GTS_T?

Thank you

Upvotes: 1

Views: 880

Answers (1)

DineshDB
DineshDB

Reputation: 6193

Use DISTINCT inside your Sub-Query.

Try this:

;with GTS_cte AS
    (SELECT [BusinessTermID], GTS_T = 
    STUFF                   ((SELECT DISTINCT ', ' + dbo.TblField.GTS_table
                             FROM      dbo.TblField
                             WHERE   [BusinessTermID] = Y.[BusinessTermID] AND dbo.TblField.GTS_table <> '' FOR XML PATH('')), 1, 2, '')
     FROM      dbo.Tblfield AS Y
     GROUP BY [BusinessTermID])

    ,
  syn_cte as (
SELECT [BusinessTermID], syns = STUFF
                      ((SELECT DISTINCT ', ' + dbo.TblBusinessSynonym.Synonym
                        FROM      dbo.TblBusinessSynonym
                        WHERE   [BusinessTermID] = x.[BusinessTermID] AND dbo.TblBusinessSynonym.Synonym <> '' FOR XML PATH('')), 1, 2, '')
FROM     dbo.TblBusinessSynonym AS x
GROUP BY [BusinessTermID])


select syn_cte.BusinessTermID, syn_cte.syns, GTS_cte.GTS_T 
from  syn_cte  join 
  GTS_cte on GTS_cte.BusinessTermID = syn_cte.BusinessTermID

Upvotes: 2

Related Questions