Sunday, January 19, 2014

LINQ - Concat a column value by a Unique ID

Table 1:

Number               City       
111                         Delhi     
111                         Agra      
112                         Jaipur   
111                         Hapur   
113                         Jodhpur              

Table 2

Number(Unique)  Percent
111                                         10
112                                         20
113                                         30


Output Required

Number                               Cities                             Percent
111                         Delhi ,Agra ,Hapur                           10
112                                         Jaipur                               20
113                                         Jodhpur                            30


By Linq

var varContact = from t2 in Table2.AsEnumerable()
                                    
                 join t1 in Table1.AsEnumerable() on t1.Field("Number") equals t2.Field("Number") into t1t2
                 select new
                 {
                                     Number = t2.Field("Number"),
                     Cities = t1t2.Where(x => x.Field("Number").ToString().Equals(c.Field("Number").ToString())).Select(g => g.Field("City")).Aggregate((i, j) => i + " ," + j),
                     Percent = c.Field("Percent")
                                        

                 };

Split the String values with a special character in MS Flow to convert this into Array

 Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...