c# datatable query for retriving similar records comma separated string

Hi,
Try below Extension method.

public static class Extensions
{
/// <summary>
/// Get the Column values as comma separated.
/// </summary>
/// <param name="value">Your Data Table</param>
/// <param name="columnfilter">Name of Column on the basis you want to filter</param>
/// <param name="filterParam">filter value</param>
/// <param name="columnName">Column's values that needs to be comma separated</param>
/// <returns></returns>
public static string GetCommaSeparatedValue(this DataTable value, String columnfilter, String filterParam, String columnName)
{
            string retVal = String.Empty;
            if (value.Rows.Count > 0)
            {
                var rowColl  = value.AsEnumerable();
                retVal   = String.Join(",",(from r in rowColl where r.Field<string>(columnfilter) == filterParam  select r.Field<string>(columnName)));
            }
            return retVal.ToString();
        }
}
</string></string>

Now use this to get comma separated values.

//Sample data.
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Movies", typeof(String)));
dt.Columns.Add(new DataColumn("TvSeries", typeof(String)));

dt.Rows.Add("1", "Prison Break");
dt.Rows.Add("1", "How i Met your mother");
dt.Rows.Add("3", "Lost");
dt.Rows.Add("2", "Prison Break");

//now get the  values from DataTable.
var str = dt.GetCommaSeparatedValue("Movies", "1", "TvSeries");

you can use this anywhere windows or asp.net.
As this sample code you can refine according to your need.

Hope this helps!

Hi,

public class Row
{
    public int msgid { get; set; }
    public string empname { get; set; }
}

static void Main(string[] args)
{
    List<Row> myDatabase = new List<Row>()
    {
        new Row(){  msgid = 1, empname="akshay"},
        new Row(){  msgid = 1, empname="sameer"},
        new Row(){  msgid = 1, empname="akash"},
        new Row(){  msgid = 4, empname="sidd"},
    };

    string comma =", ";

    int id = 1;
    

    string output = string.Join(comma, (from Row item in myDatabase
                                        where item.msgid == id
                                        select item.empname));
}

-- Suppress data loading messages
SET NOCOUNT ON
if OBJECT_ID('tempdb..#TeamInfo')>0 DROP TABLE #TeamInfo
-- Create Sample Data
CREATE TABLE #TeamInfo
( srno int IDENTITY,
msgid int,
empname varchar(50)
)

-- Load Sample Data
INSERT INTO #TeamInfo VALUES ( 1, 'Jim' )
INSERT INTO #TeamInfo VALUES ( 1, 'Mary' )
INSERT INTO #TeamInfo VALUES ( 1, 'Bob' )
INSERT INTO #TeamInfo VALUES ( 2, 'Sue' )
INSERT INTO #TeamInfo VALUES ( 2, 'Ralph' )
INSERT INTO #TeamInfo VALUES ( 2, 'Ellen' )
INSERT INTO #TeamInfo VALUES ( 3, 'Bill' )
INSERT INTO #TeamInfo VALUES ( 3, 'Linda' )

--Retrieve desired data
SELECT
t1.msgid,
MemberList = substring(
---begin string
(
SELECT ( ', ' + empname )
FROM #TeamInfo t2
WHERE t1.msgid = t2.msgid
ORDER BY
msgid,
empname FOR XML PATH( '' )
),
3, -- start position
1000 -- end position
)
FROM #TeamInfo t1
GROUP BY msgid

---- Results
--msgid MemberList
--1 Bob, Jim, Mary
--2 Ellen, Ralph, Sue
--3 Bill, Linda