how to remove the duplicated rows value in Sql server and bind them in gridview

i have an table as products colums productid, productname, product value, the values in the colums are .

productid productname productQuantity
1         a1          2
1         a2          2
1         a3          2
1         a4          2
1         a5          2
2         a21         3
2         a22         3
2         a23         3

so when i bind in gridview i need to get the output like this

productid  productname         productQuantity
1          a1,a2,a3,a4,a5          2
2          a21,a22,a23             3

so how can i get 开发者_如何学JAVAthe data in this format when i bind to gridview. any help how to write Sp so that i can i get data in this format would be great . or these there any other way to achive the data in this format any help would be great thank you

This should get you pretty close (in .net 3.5):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Quantity { get; set; }

        public static Product FromIDataReader(System.Data.IDataReader reader)
        {
            Product product = new Product();

            product.Id = reader.GetInt32(reader.GetOrdinal("ProductId"));
            product.Name = reader.IsDBNull(reader.GetOrdinal("ProductName")) ? 
                string.Empty : reader.GetString(reader.GetOrdinal("ProductName"));
            product.Quantity = reader.IsDBNull(reader.GetOrdinal("ProductQuantity")) ?
                0 : reader.GetInt32(reader.GetOrdinal("ProductQuantity"));

            return product;
        }
    }

    public static class LinqExtensions
    {
        public static string ToCsv(this IEnumerable<string> items)
        {
            System.Text.StringBuilder sb = new StringBuilder();

            foreach(string item in items)
            {
                sb.Append(item);
                sb.Append(", ");
            }

            return sb.ToString().Trim(' ', ',');
        }
    }

    class Program
    {
        static IEnumerable<Product> GetProductsFromDatabase()
        {
            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection("Some Connection String"))
            {
                string sql = @"some sql query that returns productid, productname, and productQuantity columns";

                using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection))
                {
                    connection.Open();

                    using (System.Data.IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            yield return Product.FromIDataReader(reader);
                    }
                }
            }
        }

        static void Main(string[] args)
        {
            IEnumerable<Product> products = GetProductsFromDatabase().ToList();
            //  This is the bit you're really looking for
            products = (from product in products group product by product.Id into p select new Product() { Id = p.Key, Name = p.Select(i => i.Name).ToCsv(), Quantity = p.Sum(i => i.Quantity) });
        }
    }
}