concatenating column value from multiple rows based on ordering sequence logic in another column

Question

On SQL server 2008 I have following results

id  combo                  name   value
-----------------------------------------
1   var1 + var2            var1   123
1   var1 + var2            var2   456
2   var1 + var4 + var2     var1   789
2   var1 + var4 + var2     var2   432
2   var1 + var4 + var2     var4   600
3   var1 + var2 + var3     var1   654
3   var1 + var2 + var3     var2   987
3   var1 + var2 + var3     var3   654

I need to flat the list so the instead of combo I'll get values for that combo.

id     values   
-----------------------------------------
1      123.456
2      789.600.432
3      654.987.654

Edit: Please check the combo column. That is the key column how value should be merged. So second item is 789.600.432 and not 789.432.600

possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Dai, Sep 21 '15 at 23:17
No more a duplicate question as http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string does not addres ordering logic. — DhruvJoshi, Sep 22 '15 at 06:20

DhruvJoshi · Accepted Answer · 2015-09-22T06:17:45.903

Please try the below query. It has been updated based on your question.

select distinct id,
stuff(
            (
                Select 
                     '.'+t1.[value]
                from tbl t1 
                    where t1.ID=t2.ID 
                    order by CHARINDEX(t1.name,t1.combo)
                for xml path('') 
            ),1,1,'') 
            as [values]
from tbl t2

Demo sql fiddle link: http://sqlfiddle.com/#!3/ba4a1/12

Explanation: The query uses FOR XML PATH in outer query to get concatenated values per id row in correct order using position of name in combo string.

concatenating column value from multiple rows based on ordering sequence logic in another column

1 Answers1