Display multiple table rows in a single Gridview row?

I have two tables:   

transportreply
|id|  |submitby|  |transport|


2008
|id|  |liftclub|  |liftclubsize|  |workfromhome|  |leave|  |submitby|

In the transportreply, there are multiple rows with information submitted by the same user originating from a checkboxlist.  What I need is an inner join of the two tables that display all the results on one Gridview row.  Currently my code looks like this:

Results.aspx
<table align="center" width="900" bgcolor="#E6E6E6" class="table3"><tr><td>
<div class="td4" align="center"><u>Results of the Employee Travel Survey
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sustainabilityConnectionString %>"
        SelectCommand="SELECT transportreply.submitby, transportreply.transport, [2008].liftclub, [2008].liftclubsize, [2008].workfromhome, [2008].leave, [2008].submitby AS Expr1 FROM transportreply INNER JOIN [2008] ON transportreply.submitby = [2008].submitby">
    </asp:SqlDataSource>
    </u></div></td></tr>
<tr><td align="center">    
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
        AllowSorting="True" AutoGenerateColumns="False" BackColor="White"
        BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3"
        DataSourceID="SqlDataSource1" GridLines="Vertical">
        <Columns>
            <asp:BoundField DataField="submitby" HeaderText="User"
                SortExpression="submitby" />
            <asp:BoundField DataField="transport" HeaderText="Transport to Work"
                SortExpression="transport" />
            <asp:BoundField DataField="liftclub" HeaderText="Lift Club"
                SortExpression="liftclub" />
            <asp:BoundField DataField="liftclubsize" HeaderText="Lift Club Size"
                SortExpression="liftclubsize" />
            <asp:BoundField DataField="workfromhome" HeaderText="Work From Home"
                SortExpression="workfromhome" />
            <asp:BoundField DataField="leave" HeaderText="Annual Leave"
                SortExpression="leave" />
            </Columns>
            </asp:GridView>

 

But this creates the following gridview display:

[User] [Transport to Work] [Lift Club] [Lift Club Size] [Work from Home]
Bsmith        bus                       0                0                     10-15 days
Bsmith        car                       0                0                     10-15 days
Bsmith        walk                     0                0                     10-15 days
Bsmith        taxi                      0                0                     10-15 days

I would like it to display as:

 [User]    [Transport to Work]  [Lift Club] [Lift Club Size] [Work from Home]
Bsmith   bus, car, walk, taxi         0                0                   10-15 days

 

What is the best way to do this?  I looked at using SQL pivot tables, but I just do not understand how to apply it.  Please help...

0
ladyath
8/26/2008 6:18:56 AM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

5 Replies
1782 Views

Similar Articles

[PageSpeed] 51

if you are familiar with database function u can try this type of solution in your sql.

This example is done for you hopefully this may help you. convert it as per your requirements

--drop table tblEmployee
--drop function dbo.fn_mycustomfun

CREATE TABLE [dbo].[tblEmployee](
 [eid] [int] NULL,
 [ename] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [dept] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

   
INSERT INTO [dbo].[tblEmployee]([eid],[ename],[dept]) VALUES (1,'Imran1','C++')
INSERT INTO [dbo].[tblEmployee]([eid],[ename],[dept]) VALUES (1,'Imran2','C++')
INSERT INTO [dbo].[tblEmployee]([eid],[ename],[dept]) VALUES (1,'Imran3','C++')
INSERT INTO [dbo].[tblEmployee]([eid],[ename],[dept]) VALUES (2,'Imran4','test')
INSERT INTO [dbo].[tblEmployee]([eid],[ename],[dept]) VALUES (3,'Imran5','C#')
create   function dbo.fn_mycustomfun(@id int)  -- your customized funcition
returns nvarchar(4000)
as
begin
declare @otherwords nvarchar(4000)  
select @otherwords = isnull(@otherwords,'') + ',' + convert(nvarchar,ename)
from tblEmployee where eid=@id
return right(@otherwords, len(@otherwords) - 1)
end

select eid,dbo.fn_mycustomfun(eid) from tblEmployee
group by eid


 


Thanks,
Kamrul Hassan

please mark as answer if it helps u.
0
kamrul3d
8/26/2008 8:53:02 AM

Thanks for the rpely, but I am not sure how to apply your example :(

I've been going through http://msdn.microsoft.com/en-us/library/aa479353.aspx and I am trying to adapt it to my code.  I now have the following in my cs file:

protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e)

{

// For each DataRow in the GridView,

// programmatically access the BulletedList, filter

// the DataView based on the GridView row's

// EmployeeID value and bind the filtered DataView

// to the BulletedList

if (e.Row.RowType == DataControlRowType.DataRow)

{

BulletedList bl =

(BulletedList)e.Row.FindControl("blttransport");

transportData.RowFilter = "submitby = " +((DataRowView)e.Row.DataItem)["submitby"].ToString();

bl.DataSource = transportData;

bl.DataBind();

}

}

DataView transportData;

// this DataView will hold all of the Territories, loaded at Page_Load

protected void Page_Load(object sender, EventArgs e)

{

// Load all of the territories into a DataView from the SqlDataSource

transportData =

(
DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);

}

 But because my 'submitby' looks like domain\username, I get the following error:  System.Data.SyntaxErrorException: Syntax error: Missing operand after '\xxxusernamexxx' operator.

The following line seems the be the problem:  transportData.RowFilter = "submitby = " +

I am thinking I somehow need to escape the \ symbol or something?

0
ladyath
8/26/2008 9:19:57 AM

 create a database function using this script

create   function dbo.fn_mycustomfun(@subby nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @otherwords nvarchar(4000) 
select @otherwords = isnull(@otherwords,'') + ',' + convert(nvarchar,transport)
from transportreply where submitby=@subby
return right(@otherwords, len(@otherwords) - 1)
end

test it by this following SQL

select dbo.fn_mycustomfun('Bsmith')

if it return's  bus, car, walk, taxi

then change your actual sqldatasource by this 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sustainabilityConnectionString %>"
        SelectCommand="SELECT transportreply.submitby,,dbo.fn_mycustomfun(transportreply.submitby), [2008].liftclub, [2008].liftclubsize, [2008].workfromhome, [2008].leave, [2008].submitby AS Expr1 FROM transportreply INNER JOIN [2008] ON transportreply.submitby = [2008].submitby GROUP BY transportreply.submitby ">


Thanks,
Kamrul Hassan

please mark as answer if it helps u.
0
kamrul3d
8/26/2008 10:12:19 AM

Thanks for the info :)  I created the function successfully and tested it using domain\username of a user already in the database and it worked fine.  However, my gridview does not display anything and I assume it somehow needs to change to work with the new SQLDataSource?  Before I could just select the fields, but what should they be now?  I reset my gridview and it now looks like this:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
        AllowSorting="True" AutoGenerateColumns="False" BackColor="White"
        BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3"
        DataSourceID="SqlDataSource1" GridLines="Vertical">
        <FooterStyle BackColor="#CCCCCC" Font-Size="Small" ForeColor="Black" />
        <RowStyle BackColor="#EEEEEE" Font-Size="X-Small" ForeColor="Black" />
        <EmptyDataRowStyle Font-Size="Small" />
        <PagerStyle BackColor="#999999" Font-Size="Small" ForeColor="Black"
            HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" Font-Size="Small"
            ForeColor="White" />
        <HeaderStyle BackColor="#000084" Font-Bold="True" Font-Size="Small"
            ForeColor="White" />
        <EditRowStyle Font-Size="X-Small" />
        <AlternatingRowStyle BackColor="#DCDCDC" Font-Size="X-Small" />
    </asp:GridView>

 

How do I get it to display according to the datasource you gave me?  Thanks for all the help so far, it is much appreciated!

0
ladyath
8/26/2008 10:49:01 AM

I managed to get it working, thanks!!!

0
ladyath
8/26/2008 11:00:16 AM
Reply:

Similar Artilces:

how to display multiple rows of a table in single row
DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2    anitha123:DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp...

Can you display data from a single row in multiple rows using a GridView?
I am currently displaying 3 fields in a gridview and it looks something like this:First Name Last Name Age ======== ======== === Chris Messineo 40 John Smith 35 Tim Jones 30I need to modify the gridview and display a bio, but I want it to display on 1 full line below each line, like this:First Name Last Name Age ======== ======== === Chris Messineo 40 Chris was born in a log cabin... John Smith 35 At the age of 10 John invented... Tim Jones 30 Mr. Jones was always a curio...

Add new controls to a gridview row; when editing that row using the edit row feature in the gridview control.
Hi all  I have a questions that looks hard / complex to me, and i know you can help me in this. Q {  I have row in a Gridview and i have an edit option to it. I press the edit button, to edit the values in that row and update data all the way back to the database. Now when i press the edit button, i want a drop down list to be displayed that contain values for a particular field in my grid view. (for now only plain text boxes appear.) } I hope you understand my question, all i need to do is replace the text box controls with a dropdown list when i am editing a row in ...

Data Controls insert rows into gridview then insert rows from gridview into SQL Table
Hello there, I'm still new at performing miracles with code . Currently I am having trouble with my latest coding adventure I have a web application which records jobs and labour assigned to jobs. The issue in particular I'm having is the way in which labour is to be assigned. I want to use standard controls (Textbox, Dropdownlist) to record the values into a gridview (ie,put multiple rows into the gridview using the controls). Then, when all the inserting is complete, the user clicks a submit button to insert all the rows from the gridview into a database table. The reason for...

add data from multiple rows in table to one row in gridview
 heya is there any way of adding data from multiple rows in table to one row in gridview. eg. ipc                 column 1 ,            2 ,                       3,                 4  1,    11111                 33                  &nbs...

new row below selected row in gridview to display child gridview
Hello,   I have a gridview that up selected..shows the nested child grivdview. The problem I currently have is that it stuffs all the data into a column whcih blows the whole tbale out of whack. What I need to do is create a new row below the sleected row to house the new child grivdview. I have seen third party controls do this such as teleriks radgrid but I wont to do this with the gridview control.  Any articles on how to do this? Does AJAX support this? Any help is greatly appreciated.    ...

Select rows from a table based on multiple rows of another table
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Inserting all rows of a datatable into Oracle Table in a single shot, not row by row using C#
Hi All... I have imported the data of CSV File into Datable using C#. Now i need to insert the content of DataTable to the table in Oracle Database in a single shot, without looping through the rows of the datatable. Is this Possible? Please help me out by providing the sample code. Below is sample code for Importing the CSV Data to Dataset string connString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Text;",Path.GetDirectoryName(filename)); string cmdString =string.Format("SELECT * FROM {0}", Path.G...

Select rows from a table based on multiple rows of another table #2
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. Sorry technical problems! I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. I have a table which holds all of my project info and I have another table which holds discriptive project codes like project type, project category, progect life cycle.So in the pro...

Select rows from a table based on multiple rows of another table #4
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Select rows from a table based on multiple rows of another table #3
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

HTML table row
Hi, I am using asp.net 3.5.Below is the code from markup area, my requirement is - I placed 2 options buttons on the page. when user click on first button the first row should be (2nd row shoud be hidden here)visible, if select 2nd button the 2nd row should be visible in the same place as the first row (1st row shoud be hidden here)- something similar we do in windows programming,IS POSSIBLE<asp:TableRow ID="TableRow34" Height=30 runat="server" style="font-size:11px;color:#0033FF"> <asp:TableCell ID="TableCell74" columnSpan=4 HorizontalAlign =...

select multiple rows with its row highlighted in GridView
Hello everyone, I'm not using any checkbox or select command to select the row in the GridView, I use the "RowDataBound" event in the GridView for the "onmouseover" click and row select.  That works fine for me but I want to have the functionality to select a multiple rows with its row highlighted and have a row selected count. For example, If I select a row, it hightlights that row and if I select another row, it will do the same thing and have the previous row still highlighted.  I have been looking for a way to implement this but can't find a ...

Update GridView and display to client row by row
 Hi, I am working on a website in which I have to display the data using gridview. The requirement is initially the gridview will be displayed with certain data. Later when user clicks abutton then we need perform a complex operation on each which will take some time. Once the operation is completed we need to update the gridview with operation results.This process should done for all the rows in the gridview. Right now if the user clicks on the button the operation is being performed on all the rows and all the results are in the gridview at once. For all the operation to be ...

Web resources about - Display multiple table rows in a single Gridview row? - asp.net.presentation-controls

Code division multiple access - Wikipedia, the free encyclopedia
Code division multiple access ( CDMA ) is a channel access method used by various radio communication technologies. It should not be confused ...

Man stabbed multiple times in street fight
A 23-year-old man suffers multiple stab wounds in a wild street brawl involving up to 100 people.

Man charged with murder of 21yo found on road in Tewantin with multiple stab wounds
Police charge a man with the murder of a 21-year-old man found lying on a road with multiple stab wounds to his chest.

Multiple shots fired at Atlanta mall - abc13.com
Multiple shots were fired Saturday evening in a suburban Atlanta mall, prompting a frantic rush by shoppers to evacuate.

Nigeria: Over 70 Feared Killed in Borno, Adamawa Multiple Blasts
More than 70 people were feared killed in multiple bomb attacks believed to have been carried out by Boko Haram militants and their agents in ...

Facebook is testing multiple topic-based News Feeds for mobile devices
... the information you want to see at the appropriate times. According to the screenshots taken by Jason Stein, Facebook looks to be testing multiple, ...

Miami teen shot multiple times at grandma's home
The victim reportedly remains in the hospital as police continue to search for the suspect

Gov. Jerry Brown pardoned Robert Downey Jr. for his multiple crimes in the ā€™90s
Iā€™m old enough to remember when Robert Downey Jr. was an utter mess. Back in the ā€˜90s, I lost count of how many times he was arrested, how many ...

Gas tank explodes at plant in Nigeria; multiple fatalities reported
CNN Gas tank explodes at plant in Nigeria; multiple fatalities reported CNN Kano, Nigeria (CNN) An industrial gas tank exploded in southern ...

Draymond explains how multiple Warriors lead in their own way
... Search 42° Navigation Home Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Draymond explains how multiple ...

Resources last updated: 12/31/2015 8:09:38 AM