Get all column valus of a datatable based on only one distinct column

Question

I have a table with 10 columns and I want to get all values of each row but only of distinct IDs.

I have this so far:

DataTable Distinct = view.ToTable(true, "ID");

But this creates a DataTable that only contains the ID column. I want a DataTable with distinct ID's but containing all column values(there can be duplicates for the other columns.) Is there a simple way of doing this?

What is view ?? and **ToTable** is it any extension method ?? Specify your requirement... — Krishnraj Rana, Sep 23 '14 at 15:10
What do you mean "distinct ID's but containing all column values", if you have a 2 or more records with the same id column but different other columns then that row isn't distinct. — Ben Robinson, Sep 23 '14 at 15:14
There is no way to do what you are thinking of doing here. Here's why. Say you have two rows with different data, but the same ID of "1". You cannot have two rows for the different columns, but only one row for the ID. — Chuck Buford, Sep 23 '14 at 15:24

score 0 · Answer 1 · answered Dec 17 '14 at 07:11

One way would be to use LINQ for this job:

using System;
using System.Collections.Generic;
using System.Data;      
using System.Xml.Serialization;
using System.Linq;
using System.Data.Linq;
using System.Data.DataSetExtensions;

public class Program
{
    public static void Main()
    {
        using (DataTable dt = new DataTable("MyDataTable"))
        {
            // Add two columns.
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));         

            // Add some test data.
            Random rnd = new Random();
            for(int i = 0; i < 7; i++)
            {
                DataRow dr = dt.NewRow();
                dr["Id"] = rnd.Next(1, 4);
                dr["Name"] = "Row-" + (i + 1);
                dt.Rows.Add(dr);
            }

            Console.WriteLine("All rows:");
            PrintResults(dt.AsEnumerable());

            var results = dt
                .AsEnumerable()
                .GroupBy(x => (int)x["Id"])
                .Select(g => g.First());

            Console.WriteLine("Distinct rows:");
            PrintResults(results);
        }
    }

    private static void PrintResults(IEnumerable<DataRow> rows)
    {
        foreach(var row in rows)
        {
            Console.WriteLine(
                String.Format(
                    "Id: {0}, Name: {1}", 
                    (int)row["Id"], 
                    (string)row["Name"]));
        }   
    }
}

dotnetfiddle

Get all column valus of a datatable based on only one distinct column

1 Answers1

Linked