Concatenate Columns based on 2 column values

Question

I have a table as follows, lets say TEMP

Desc_1 Desc_2 Score
a      b      10
a      c      10
x      y      10
x      z      10
a      d      9
a      e      9
a      f      8
f      g      8

I want the result as follows

a,b,c 10
x,y,z 10
a,d,e 9
a,f,g 8

So the first rules is all elements in a group should have the same score.

And how the grouping occurs is

1)There should be a direct relation that is a,b should be in the table TEMP

2)There could be an indirect relation that is a,g could be in the same group if there are entries in TEMP as [(a,f) and (f,g) ]or [(a,f) and (g,f))]

P.S. Sorry for the constant edits.

This can be interpreted in many ways.. Add column name in result — Pரதீப், Jul 11 '17 at 14:54
You distinguish between a,b,c and x,y,z on what basis? What about a,b,c and d,e,f? — cloudsafe, Jul 11 '17 at 15:02
Sorry for being so late in commenting. I have edited the question . Hope it helps. — Durvesh Patil, Jul 12 '17 at 15:38

score 1 · Answer 1 · answered Jul 11 '17 at 14:43

Try

 SELECT score, DESC_1 FROM yourTable 
 UNION 
 SELECT score, DESC_2 FROM yourTable

Then you use STUFF and XML PATH based on score as show in this answer

How can I combine multiple rows into a comma-delimited list in SQL Server 2005? https://stackoverflow.com/a/180375/3470178

Kannan Kandasamy · Answer 2 · 2017-07-11T14:53:08.180

1

You can use stuff and group by as below:

;With cte as (
    Select Desc_1 as descr, score from #desc
    union 
    Select Desc_2 as descr, score from #desc
) select Score, 
    stuff((select distinct ',' + descr from cte where score = d.score for xml path('')),1,1,'')
    from cte d
    group by Score

Output as below:

+-------+-------+
| Descr | Score |
+-------+-------+
| a,f,g |     8 |
| a,d,e |     9 |
| a,b,c |    10 |
+-------+-------+

If you are using SQL Server 2017 or SQL Azure then you can use string_agg as below

...cte
Select String_Agg(descr, ','), Score from cte
   group by Score

edited Jul 11 '17 at 14:53

answered Jul 11 '17 at 14:46

Kannan Kandasamy

13,405
3
25
38

How first four rows for 10 gets into two rows? Based on what indicator? – Kannan Kandasamy Jul 11 '17 at 14:54
Oh , so they have the same element in either desc_1 or desc_2. like a is common in the first 2 rows and x is common in next two rows. – Durvesh Patil Jul 11 '17 at 14:56
then how about 8? – Kannan Kandasamy Jul 11 '17 at 14:57

score 1 · Answer 3 · answered Jul 11 '17 at 14:58

STUFF and XML Path to concatenate:

;with s as (Select distinct score from #scores),
fs as (
select s.score, d1.Desc_1 as col
from s
left join #scores d1 on d1.score=s.score
UNION
select s.score, d2.Desc_2 as col
from s
left join #scores d2 on d2.score=s.score)
SELECT  
       Columns = STUFF((SELECT ',' + col 
                          FROM fs sc
                          where sc.score=fs.score
                        FOR XML PATH('')),1,1,'')
        ,score
  FROM fs 
  group by score
  order by score desc

score 1 · Accepted Answer · answered Jul 11 '17 at 20:12

This appears to be returning the desired results...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    Desc_1 CHAR(1),
    Desc_2 CHAR(1),
    Score INT 
    );

INSERT #TestData (Desc_1, Desc_2, Score) VALUES
    ('a', 'b', 10),
    ('a', 'c', 10),
    ('x', 'y', 10),
    ('x', 'z', 10),
    ('a', 'd', 9),
    ('a', 'e', 9),
    ('a', 'f', 8),
    ('f', 'g', 8);

--SELECT * FROM #TestData td;

--============================================

WITH
    cte_UnPivValues AS (
        SELECT DISTINCT 
            td.Desc_1, 
            td.Score, 
            upv.UnPivValue
        FROM
            #TestData td
            CROSS APPLY ( VALUES (td.Desc_1), (td.Desc_2) ) upv (UnPivValue)
        )
SELECT 
    Descr = STUFF((
        SELECT 
            CONCAT(',', upv2.UnPivValue)
        FROM 
            cte_UnPivValues upv2
        WHERE 
            upv1.Desc_1 = upv2.Desc_1
            AND upv1.Score = upv2.Score
        ORDER BY
            upv2.UnPivValue
        FOR XML PATH ('')

    ), 1, 1, ''),
    upv1.Score
FROM
    cte_UnPivValues upv1
GROUP BY 
    upv1.Desc_1,
    upv1.Score
ORDER BY 
    upv1.Score DESC,
    Descr ASC;

Concatenate Columns based on 2 column values

4 Answers4