Display records from another table in an existing GridView

Question

Here is an example of the problem I am facing. I have a table called Issues, which has the initial record information. Each record also has a category and some have multiple categories.

Issues

ID | Name        | Comments        | Date     | SubmittedBy
1  | Server down | Need assistance | 6/1/2015 | John

IssuesCategories

ID | Name
1  | Internal
2  | External
3  | Mobile
4  | Email

IssuesCatList

QID | CID
1   | 1
1   | 3
1   | 4

I am trying to display all of this in one GridView that looks like this:

Name        | Categories              | Date     | By
Server down | Internal, Mobile, Email | 6/1/2015 | John

So I want to place the category names into the a column of the gridview. How can I accomplish this? Can this be done with the SQL query or do I need to do this in the RowDataBound in the code behind?

<asp:GridView runat="server" ID="issuesView" CssClass="table" OnRowDataBound="issuesView_RowDataBound"
    GridLines="None" EmptyDataText="No issues" AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Date" DataFormatString="{0:M/d/yy}" HeaderText="Date" />
        <asp:BoundField DataField="SubmittedBy" HeaderText="By" />
    </Columns>
</asp:GridView>

@fubo That is dealing with one table, I am trying to concatenate my categories into one field where their ID matches the ID of the main table. — techora, Jun 03 '15 at 15:09
@fubo. It is not a dupe of the linked question, because that question loads a single variable. This question requires a concatenated string in the form of a column, not a variable. But it is a dupe of the question linked in the selected answer. — PerformanceDBA, Jun 03 '15 at 19:27

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

SELECT 
  [ID],
  name,
  STUFF((
    SELECT ', ' + [Name]  
    FROM #IssuesCategories cats
    JOIN #IssuesCatList list on list.CID = cats.ID
    WHERE (list.QID = #Issues.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
  ,[date]
  ,SubmittedBy
FROM #Issues

Credit to this post for the awesome STUFF expression.

edited May 23 '17 at 12:14

Community

1
1

answered Jun 03 '15 at 15:55

MartianCodeHound

355
1
8

Display records from another table in an existing GridView

1 Answers1