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? — Dai
– Dai, Commented Sep 21, 2015 at 23:17
No more a duplicate question as stackoverflow.com/questions/194852/… does not addres ordering logic. — DhruvJoshi
– DhruvJoshi, Commented Sep 22, 2015 at 6:20

DhruvJoshi · Accepted Answer · 2015-09-22 06:17:45Z

3

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.

edited Sep 22, 2015 at 6:17

answered Sep 22, 2015 at 0:06

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related