Asp.net c# multiple value in a single column of gridview

Question

How can I show multiple value in a single column of a GridView? For example when I search "Ivan" in the TextBox, the output will return multiple rows of Ivan like this:

Name Task
Ivan Task1
Ivan Task2
Ivan Task3

I want something like this

Name Task
Ivan Task1, Task2, Task3

My Table is like this

Employee (id,name)
Task(id,name)
EmployeeTask(employee.id,task.id)

Here is my sql code

SELECT  e.name,  t.name
FROM EmployeeTask et
INNER JOIN employee e ON e.id = et.employee_id
INNER JOIN task t ON t.id = et.task_id
WHERE e.name = @Name

And this is my GridView mark up

<Columns>
    <asp:BoundField DataField="name" HeaderText="Name" SortExpression="name"/>
    <asp:BoundField DataField="task" HeaderText="Task" SortExpression="task"/>        
</Columns>

Answer 1

In pure SQL, here's a way to get your desired result which you can then feed into the GridView:

SELECT     e.name, REPLACE(REPLACE(REPLACE
                          ((SELECT     t.name
                              FROM         EmployeeTask AS et INNER JOIN
                                                    task AS t ON t.id = et.task_id
                              WHERE     (et.employee_id = e.id)
                              FOR XML RAW(''), ELEMENTS)
                   , '</name><name>', ', '), '<name>', ''), '</name>', '') AS EmployeeTasks
FROM         employee AS e
WHERE     (e.name = @Name)

Essentially, it's a subquery that flattens the tasks into XML output and then replaces the tags with commas.

Answer 2

You have to change your SQL query.

• If you are using MySQL you can use group_concat.
• If you are using MSSQL you can find your answer here

Answer 3

You need to concatenate the Task name's using SQL something like this, mind you I have not tested the script

    DECLARE @CTasks VARCHAR(500)
    SET @CTasks = ''

    SELECT @CTasks = @CTasks  + t.Task + ', ' FROM Employee e
    INNER JOIN Tasks t ON t.EmployeeId = e.Id
    WHERE e.Name = @Name
    ORDER BY e.Name ASC

    IF LEN(@CTasks) > 0
    SELECT @Name As Name, @CTasks As Tasks

You might end up getting a ',' at the end that needs taking care from Tasks column

Hope this helps...

Answer 4

If you Are using mssql server 2008 then there is an introduction to the pivot functions which does the same thing you mentioned in the question

How to Use Pivot

Hope you find helpful

Answer 5

In Oracle:

select name,wm_concat(task) as task from table name group by name

I dont know in sql Server.But i think it may help u.

Similar Type Question, See it

In Sql:

SELECT Field1, Substring(Field2, 2, LEN(Field2)) AS Field2 FROM
(
    SELECT
        [InnerData].Field1,
        (SELECT  ',' + Field2 FROM @Fields WHERE Field1=[InnerData].Field1 FOR XML PATH('')) AS Field2
        FROM
        (
            SELECT DISTINCT Field1 FROM @Fields
        ) AS [InnerData]
) AS OuterData

I got this Query from Below link

Refer this link

Answer 6

Create a new class:

Class EmployeeWithTask{ string employeeName{get;set} list<string>employeeTasks {get,set}}

Create an EmployeeWithTask list.after you retrive the list of tasks, for every distinct employee in the result loop through the results and create one instance of EmployeewithTask . Finally bind the EmployeeWithTask list to your gridview