CODEWITHSUNDEEP
sqlsql-server
user4963104
user4963104

Reputation: 120

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

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

Upvotes: 1

Views: 1816

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17146

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.

Upvotes: 3

Related Questions