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>

Please can you copy a bit of your GridView code so I can get a idea how are you currently binding the columns — HatSoft, Jun 20 '12 at 19:53
How are you accessing your data (Entity Framework, linq-to-sql, etc.)? There are multiple ways this can be done, so it would help to know. — JamieSee, Jun 20 '12 at 20:40
Check this. This might help you http://stackoverflow.com/a/11126887/610194 — SP007, Jun 20 '12 at 19:50

score 1 · Accepted Answer · answered Jun 20 '12 at 23:18

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.

if i want to add a new line break after each task where should i use? i tried CHAR(10); but it gave me a syntax error — Ahmad Danial, Jun 23 '12 at 10:40
Modify the line as follows `, '', CHAR(13) + CHAR(10)), '', ''), '', '') AS EmployeeTasks` — JamieSee, Jun 25 '12 at 16:36

score 1 · Answer 2 · edited May 23 '17 at 12:26

1

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

edited May 23 '17 at 12:26

Community

1
1

answered Jun 20 '12 at 23:22

Ali

808
2
11
20

score 0 · Answer 3 · answered Jun 21 '12 at 00:12

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...

score 0 · Answer 4 · answered Jun 21 '12 at 01:59

0

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

answered Jun 21 '12 at 01:59

Vishal Sharma

2,773
2
24
36

score 0 · Answer 5 · edited May 23 '17 at 12:26

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.

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

6 Answers6