CODEWITHSUNDEEP
sqlsql-serverdistinct
Scott Holtzman
Scott Holtzman

Reputation: 27239

SQL Concatenate Distinct Values

I have the following query where I am concatenating a list of products and quotes by account and version number. 

SELECT DISTINCT ST2.Account_No, ST2.version_num,
    substring((SELECT  ',' +  ST1.ProductNo  AS [text()]
            FROM  (SELECT DISTINCT Account_No, version_num, ProductNo, QuoteNo, RowNo
                    FROM uAccountProductInfo) ST1
            WHERE ST1.version_num = ST2.version_num  
                AND ST1.Account_No = ST2.Account_No  
            ORDER BY ST1.RowNo, ST1.Account_No,ST1.version_num
            FOR XML PATH (''))
    , 2, 1000) [AllProduct]
    ,
    substring((SELECT  ','+  ST3.QuoteNo  AS [text()]
            FROM (SELECT DISTINCT Account_No, version_num, ProductNo, QuoteNo, RowNo
            FROM [uAccountProductInfo]) ST3
            WHERE ST3.version_num = ST2.version_num  
                AND ST3.Account_No = ST2.Account_No  
            ORDER BY ST3.RowNo, ST3.version_num 
            FOR XML PATH (''))
    , 2, 1000) [AllQuote]
FROM uAccountProductInfo ST2

The problem I am experiencing is that the return values are not showing the distinct results. I understand the reason it's happening but cannot figure out how to adjust it.

Return results look this:

Account    version_num     AllProduct    AllQuote    
1          2               aaa,aaa,aaa   111,111,111
1          3               aaa,aaa,bbb   111,111,222

What I want is 

Account    version_num     AllProduct    AllQuote    
1          2               aaa,          111
1          3               aaa,bbb       111,222

Test Data would be this:

Account    version_num    LOB    Package    Product    Quote    RowNo
1          2              GL     1          aaa        111      1
1          2              AU     1          aaa        111      2    
1          2              PF     1          aaa        111      3
1          3              GL     1          aaa        111      1
1          3              AU     1          aaa        111      2
1          3              WK     0          bbb        222      3

The reason they are returning with multiple instances of the same product | quote is due to the inclusion of RowNo column. I had this excluded before which returned the distinct list of values, but I need to order by RowNo so that the values come in a specific order.

I have been wracking my brain all morning but cannot figure out how to adjust the query to only return the distinct values at the top level.

Any suggestions?

n.b. - this is part of a larger query but once this subquery is resolved it should flow into main one just fine (at least I think). I can post main query if people need.

Upvotes: 2

Views: 2919

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

I much prefer stuff() rather than substring() for removing the separating character. What you require, though, is select distinct or group by in the subquery:

stuff((SELECT ',' +  ST1.ProductNo  AS [text()]
       FROM uAccountProductInfo ST1
       WHERE ST1.version_num = ST2.version_num AND
             ST1.Account_No = ST2.Account_No  
       GROUP BY ST1.ProductNo
       ORDER BY MIN(ST1.RowNo) 
       FOR XML PATH ('')
      ), 1, 1, '')

Your additional subquery is superfluous. In fact, it is misleading because you are using SELECT DISTINCT and expect it to return one row per ProductNo -- even when multiple rows exist with different values in the other columns.

Note that the ordering is unclear. This bases it on the minimum RowNo.

Upvotes: 3

Related Questions