how to create gridview which has column and row

Question

0.00/5 (No votes)

See more:

Hi all,

im having a problem to build a gridview that has column and row that display data from database .is it possible to build it? i dont know how to do it.the template in visual studio only has the column.not the row.i already tried so may times but it didnt work.please help me on how to build it and also how to bind it.

eg: Column0 Column1 Column2

Row0

Row1

Row2

pls help me.thanks.

Posted 15-Jan-12 19:49pm

musiw

Updated 15-Jan-12 19:51pm

v3

Add a Solution

2 solutions

Solution 2

Are you looking for something like Pivot by any chance?
Ref: http://unboxedsolutions.com/sean/archive/2004/08/30/302.aspx[^]

Posted 15-Jan-12 19:55pm

Prerak Patel

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2012-01-15T19:53:00

Solution 1

C#

//create the connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";

//create the database query
string query = "SELECT * FROM MyTable";

//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

//create a DataTable to hold the query results
DataTable dTable = new DataTable();

//fill the DataTable
dAdapter.Fill(dTable);

//the DataGridView
DataGridView dgView = new DataGridView();

//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();

//set the BindingSource DataSource
bSource.DataSource = dTable;

//set the DataGridView DataSource
dgView.DataSource = bSource;

dAdapter.Update(dTable);

Posted 15-Jan-12 19:53pm

RDBurmon

Comments

musiw 16-Jan-12 3:36am

my database:

table transshipment:
id
id_transshipment
no_of_train
date

table class_transshipment:
id_transshipment
desc_

id_transshipment= 1
desc_ =EMU

id_transshipment= 2
desc_ =intercity

eg: EMU Intercity
no.of trains Data Data

i want to display the data from transshipment table in row and column format.

eg:

RDBurmon 16-Jan-12 4:02am

Ok give me a minute I will revert back.

in the mean time let me know can you use stored procedure instead of query ?

musiw 16-Jan-12 4:55am

i dont use stored procedure.i just using query as usual..is it possible?cause i dont familiar using stored procedure.but if you think it is the best way, hope u can show me the code later.

RDBurmon 16-Jan-12 6:46am

Use this

SqlDataAdapter sqlcom0 = new SqlDataAdapter("SELECT * FROM ( SELECT t.no_of_train,ct.desc_ FROM @transshipment t LEFT OUTER JOIN @class_transshipment ct ON t.id_transshipment=ct.id_transshipment where t.id_transshipment in (1,2)) AS Tbl1 PIVOT (AVG (no_of_train) FOR Desc_ IN ([EMU],[Intercity])) AS no_of_train", myConnection);

DataSet ds0 = new DataSet();
sqlcom0.Fill(ds0, "transshipment");
gd_transshipment.DataSource = ds0.Tables["transshipment"].DefaultView;
gd_transshipment.DataBind();

RDBurmon 16-Jan-12 6:46am

this is the query for your reference

SELECT * FROM
( SELECT t.no_of_train,ct.desc_
FROM @transshipment t LEFT OUTER JOIN @class_transshipment ct ON t.id_transshipment=ct.id_transshipment where t.id_transshipment in (1,2)) AS Tbl1
PIVOT (AVG (no_of_train) FOR Desc_ IN ([EMU],[Intercity])) AS no_of_train

musiw 16-Jan-12 20:18pm

i just copy your code then the error appear.i remove the @ sign before because it also give error.this error below is a fter i remove the @ sign.

Could not determine a MetaTable. A MetaTable could not be determined for the data source '' and one could not be inferred from the request URL. Make sure that the table is mapped to the dats source, or that the data source is configured with a valid context type and table name, or that the request is part of a registered DynamicDataRoute.

this is the source code design:
<asp:GridView ID="gd_transshipment" runat="server"
ShowHeaderWhenEmpty="True"
AutoGenerateColumns="False" Font-Size="Small" ForeColor="Black">

<columns>
<asp:TemplateField>
<itemtemplate>
<asp:Label ID = "Label1" runat="server" Text="No. of trains">

<asp:TemplateField HeaderText="EMU">
<itemtemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("no_of_train") %>'>

<asp:TemplateField HeaderText="Intercity">
<itemtemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("no_of_train") %>'>

i dont know how to create a row in gridview..

RDBurmon 17-Jan-12 0:55am

Reading this , Will revert back ASAP.

RDBurmon 17-Jan-12 1:09am

Hello musiw ,

Below code works for me . There is no need to create row and column it will create row and column as per data from query.

Please create separate project for this and try this code

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Resources;
using System.Net;
using System.Net.Sockets;
using System.Data.SqlClient;
using System.Data;

namespace WebApplication2
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

string myConnection = "Data Source=MDT765;Initial Catalog=TST;User Id=sa;Password=sa@123;";
SqlDataAdapter sqlcom0 = new SqlDataAdapter("SELECT * FROM ( SELECT t.no_of_train,ct.desc_ FROM transshhipment t LEFT OUTER JOIN class_transshipment ct ON t.id_transshipment=ct.id_transshipment where t.id_transshipment in (1,2)) AS Tbl1 PIVOT (AVG (no_of_train) FOR Desc_ IN ([EMU],[Intercity])) AS no_of_train", myConnection);

DataSet ds0 = new DataSet();
sqlcom0.Fill(ds0);
GridView1.DataSource = ds0.Tables[0].DefaultView;
GridView1.DataBind();

}

}
}

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication2._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="GridView1" runat="server">

</div>
</form>
</body>
</html>

RDBurmon 17-Jan-12 1:15am

Also below is the database stucture and data

CREATE TABLE [dbo].[transshhipment](
[id] [bigint] NULL,
[id_transshipment] [bigint] NULL,
[no_of_train] [bigint] NULL,
[date] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[class_transshipment](
[id_transshipment] [bigint] NULL,
[desc_] [nvarchar](50) NULL
) ON [PRIMARY]

[transshhipment]
1 1 1 2012-02-01 00:00:00.000
2 2 0 2012-02-01 00:00:00.000

[class_transshipment]
1 EMU
2 Intercity

musiw 17-Jan-12 1:47am

ok.that's better.. :D but where i must put the query "where date = 2/1/2012".because the data(report) will display according to the condition which is date.i just append but it show invalid column name date...

and 1 more thing..i must label the row data.it cant be 1 0 without any label.
currently it shows
EMU Intercity
1 0

i want to label it
EMU Intercity
no. of train 1 0

i have to follow the rule.its user requirement.what to do...

RDBurmon 17-Jan-12 1:55am

Then user this query instead . It will resolve both your problem

SELECT 'no. of train' as ' ' ,* FROM ( SELECT t.no_of_train,ct.desc_ FROM transshhipment t LEFT OUTER JOIN class_transshipment ct ON t.id_transshipment=ct.id_transshipment where t.id_transshipment in (1,2) AND t.date='2012-02-01') AS Tbl1 PIVOT (AVG (no_of_train) FOR Desc_ IN ([EMU],[Intercity])) AS no_of_train

musiw 17-Jan-12 2:43am

oh great!! it works! THANKS A LOT Rahul! you really save my job... haha :D how come you think the query.im not really clever in database query.if you dont mind where can i learn it further more.

RDBurmon 17-Jan-12 2:58am

Thanks musiw , Where are you from ? India or from other countries ? regarding learning database query google is your teacher. But any way if you need any help then do let me know.

musiw 17-Jan-12 5:27am

i'm from malaysia. thanks again.. if anything i refer to you.hope you dont get bored later...

RDBurmon 17-Jan-12 5:30am

Ok Thanks and Bye For Now

musiw 17-Jan-12 21:56pm

hi..

thanks.today i manage to complete another table using your coding given yesterday.but it come to a problem when i want to create another gridview that has 2 rows.yesterday only 1 row (no.of train)...

so how to add another row below.can u explain..

thanks in advance.

RDBurmon 18-Jan-12 6:32am

Can you give me example about what type of data in second row ?

musiw 18-Jan-12 20:40pm

its integer.

the table will look like this.

EMU Security Others
Cancellation
Delay (min)

musiw 18-Jan-12 22:18pm

EMU Security Others
Cancellation 0 0 0
Delay (min) 0 49 0

RDBurmon 19-Jan-12 2:59am

Ok then in that case you have to put another grid . give me table schema and data of cancellation and deley so that I can prepare query for you.

musiw 19-Jan-12 3:20am

you mean display in two different gridview?

dbo.Cancellation
[id][bigint],
[id_class_service][int]
[cancellation][int]
[delay][int]
[date][datetime]

dbo.class_service
[id_class_service][int]
[desc_]

[cancellation]
1 1 0 0 2/1/2012
2 2 0 49 2/1/2012
3 3 0 9 2/1/2012

[class_service]
1 EMU
2 Security
3 Others

RDBurmon 19-Jan-12 3:27am

Yes, Is it possible to you ?. If yes then will provide the query with source code .

musiw 19-Jan-12 4:18am

i already try that.i manage to display in 2 different gridview.but according to a hard copy report the data is display in 1 table.fyi, this is report department which is key in data manually.never have a system before.this is the first system for that.

i also try to display it vertically.
cancellation|delay|EMU|Security|Others

i just want to ask you.is there a possible way to create this report in 1 table as i ask earlier?if it impossible then i have to standby with the answer to user later.. :)

RDBurmon 19-Jan-12 4:37am

No it is not possible through one query and through one grid view. You have to have to add another grid view for that.

musiw 19-Jan-12 20:07pm

ok then..at least i know it is possible..can u show me how to done that.because i manage to do it in 2 different gridview only not one gridview

RDBurmon 20-Jan-12 3:18am

Ok , Here is the code . I have tested it with my local client and it is working fine for me

just add this for second grid

<asp:GridView ID="GridView2" runat="server">

and add this to bind datasource to grid

StringBuilder sqlquery=new StringBuilder();
sqlquery.Append("SELECT 'Cancellation' as ' ' ,* FROM ( SELECT C.cancellation,cs.desc_ FROM Cancellation c LEFT OUTER JOIN class_service cs ON c.id_class_service=cs.id_class_service where c.date='2012-02-01') AS Tbl1 PIVOT (SUM (cancellation) FOR Desc_ IN ([EMU],[Security],[Others])) AS cancellation ");
sqlquery.Append("UNION ALL ");
sqlquery.Append("SELECT 'Delay (min)' as ' ' ,* FROM ( SELECT C.delay,cs.desc_ FROM Cancellation c LEFT OUTER JOIN class_service cs ON c.id_class_service=cs.id_class_service where c.date='2012-02-01') AS Tbl1 PIVOT (SUM ([delay]) FOR Desc_ IN ([EMU],[Security],[Others])) AS [delay]");
SqlDataAdapter sqlcom1 = new SqlDataAdapter(sqlquery.ToString(), myConnection);
DataSet ds1 = new DataSet();
sqlcom1.Fill(ds1);
GridView2.DataSource = ds1.Tables[0].DefaultView;
GridView2.DataBind();

musiw 20-Jan-12 3:33am

what is stringbuilder? is there any namespace i have to add?because it gor error

RDBurmon 20-Jan-12 3:44am

Plz add this

using System.Text;

musiw 20-Jan-12 4:19am

sorry to ask you...i dont get it.just add another gridview then put this query.where to put the 1st gridview?this query is for both right? i just copy your code.but it display nothing

RDBurmon 20-Jan-12 4:24am

You have to put the C# code below the first code and you have to put the gridview code below the first gridview code.
And this is for second query only so you have to make sure that previous code is also there in your code while adding this new code. If you still want the whole code I will share it. let me know

musiw 20-Jan-12 4:55am

if u can share it will be better.so that i can compare

RDBurmon 20-Jan-12 5:08am

OK .

Here is my ASPX code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="CP_315162._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="GridView1" runat="server">

<asp:GridView ID="GridView2" runat="server">

</div>
</form>
</body>
</html>

Here is my C# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Resources;
using System.Net;
using System.Net.Sockets;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace CP_315162
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//http://www.codeproject.com/Answers/315162/how-to-create-gridview-which-has-column-and-row/?cmt=207083#cmt2_315162
string myConnection = "Data Source=MDT765;Initial Catalog=TST;User Id=sa;Password=sa@123;";
SqlDataAdapter sqlcom0 = new SqlDataAdapter("SELECT 'no. of train' as ' ' ,* FROM ( SELECT t.no_of_train,ct.desc_ FROM transshhipment t LEFT OUTER JOIN class_transshipment ct ON t.id_transshipment=ct.id_transshipment where t.id_transshipment in (1,2) AND t.date='2012-02-01') AS Tbl1 PIVOT (AVG (no_of_train) FOR Desc_ IN ([EMU],[Intercity])) AS no_of_train", myConnection);

DataSet ds0 = new DataSet();
sqlcom0.Fill(ds0);
GridView1.DataSource = ds0.Tables[0].DefaultView;
GridView1.DataBind();

StringBuilder sqlquery=new StringBuilder();
sqlquery.Append("SELECT 'Cancellation' as ' ' ,* FROM ( SELECT C.cancellation,cs.desc_ FROM Cancellation c LEFT OUTER JOIN class_service cs ON c.id_class_service=cs.id_class_service where c.date='2012-02-01') AS Tbl1 PIVOT (SUM (cancellation) FOR Desc_ IN ([EMU],[Security],[Others])) AS cancellation ");
sqlquery.Append("UNION ALL ");
sqlquery.Append("SELECT 'Delay (min)' as ' ' ,* FROM ( SELECT C.delay,cs.desc_ FROM Cancellation c LEFT OUTER JOIN class_service cs ON c.id_class_service=cs.id_class_service where c.date='2012-02-01') AS Tbl1 PIVOT (SUM ([delay]) FOR Desc_ IN ([EMU],[Security],[Others])) AS [delay]");
SqlDataAdapter sqlcom1 = new SqlDataAdapter(sqlquery.ToString(), myConnection);
DataSet ds1 = new DataSet();
sqlcom1.Fill(ds1);
GridView2.DataSource = ds1.Tables[0].DefaultView;
GridView2.DataBind();

}

}
}

musiw 20-Jan-12 5:35am

ohh..it just some small mistake in my query i guess..but anyway thanks a lot!

lets say if i want to add some more row, just append sql query and union all for how many rows that i want..is that right?

RDBurmon 20-Jan-12 5:44am

In this case . Yes, You have to just add query and union them

RDBurmon 20-Jan-12 5:45am

And one thing . Please give your vote to this answer so that I can get some points out of it.

Thanks and let me know if anything else we can do.
--Rahul

musiw 20-Jan-12 6:24am

ok.thank you very much. i already vote your answer :)

RDBurmon 21-Jan-12 4:32am

Thanks. Let me know if we can do anything else.

musiw 25-Jan-12 21:46pm

hi rahul,

i want to display a data which look like this:

Sector Trips EMU/(%) Hybrid
location1 110 15/13.64 -
location2 106 25/23.58 -
location3 37 - -
Total 253 40/15/81 -

i have tried so many times.but the total column appear next to trips,emu & hybrid.i want to display it below.

database:

Availability EMU
id,
id_sector,
trips,
total_trips,
emu_percent,
total_emu_percent,
hybrid,
total_hybrid,
date

EMU Sector
id sector,
emu_sector

fyi, i manage to display all the data except the total data below.is there any way to complete or i have to change the database?

RDBurmon 26-Jan-12 12:14pm

Ok , I understand the structure but I need data in "Availability EMU" and "EMU Sector" table. Also please share your SQL Query to calculate above values excluding Total columns. Post that I will let you know how to write query for total columns.

musiw 26-Jan-12 19:53pm

Availability EMU

1 1 110 null 15/13.64% null - null 2/1/2012
2 2 106 null 25/23.58% null - null 2/1/2012
3 3 37 null - null - null 2/1/2012
4 null null 253 null 40/15/81% null - 2/1/2012

EMU Sector
1 Sg Gadut - Rawang - Sg Gadut
2 Bt. Caves - Pel.Klang - Bt.Caves
3 Rawang - Tg. Malim

fyi, total trips actually is permanent data.it fix 253 trips.cannot be changed.total_emu_percent & total_hybrid is manually insert by user.i already ask user for the formula but he said no need to put the formula, a staff will key in the total.

RDBurmon 27-Jan-12 2:29am

Musiw , I am still not getting the data structure for sake of clarity
Could you please share your "CREATE TABLE " sql query of "Availability EMU" and "EMU Sector" .

Also could you please again share the result of below queries as it is . Please do not modify any data in the result.

1) SELECT * FROM [Availability EMU]
2) SELECT * FROM [EMU Sector]

Thanks ,
Rahul D.

musiw 27-Jan-12 3:26am

dbo.Availability EMU
[id][bigint],
[id_sector][int]
[trips][int]
[total_trips][int]
[emu_percent][nchar]
[total_emu_percent][nchar]
[hybrid][int]
[total_hybrid][int]

dbo.emu_sector
[id_sector][int]
[sector][varchar]

and the data is like attached before.

RDBurmon 27-Jan-12 3:37am

I think below row is total values and in [Availability EMU] table right ?
<Pre>
4 null null 253 null 40/15/81% null - 2/1/2012
</Pre>

Can you please share your sql query which you have prepared ? I know it is not calculating the total but share what you have.

musiw 27-Jan-12 3:42am

yes.the below row is the total.

i display it in 2 different gridview.i try to display in 1 gridview but it display beside the columns not the below row

SqlDataAdapter sqlcom15 = new SqlDataAdapter("SELECT ct.emu_sector as 'Sector', t.trips,t.emu_percent,t.hybrid,'Total' as 'Sector' from class_emu_sector ct, emu_avail t where ct.id_sector = t.id_sector and date = '" + TextBox1.Text + "'", myConnection);
DataSet ds15 = new DataSet();
sqlcom15.Fill(ds15);
GridView7.DataSource = ds15.Tables[0].DefaultView;
GridView7.DataBind();

SqlDataAdapter sqlcom20 = new SqlDataAdapter("Select t.total_trips as 'Trips',t.total_emu_percent as 'EMU/(%)',t.total_hybrid as 'Hybrid' from emu_avail t where t.date = '" + TextBox1.Text + "'", myConnection);
DataSet ds20 = new DataSet();
sqlcom20.Fill(ds20);
GridView11.DataSource = ds20.Tables[0].DefaultView;
GridView11.DataBind();

RDBurmon 27-Jan-12 4:23am

is this code working ? Because I have created tables from the "CREATE TABLE" you have shared and ran the above code

The below error came

Msg 207, Level 16, State 1, Line 1
Invalid column name 'emu_sector'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'trips'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'emu_percent'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'hybrid'.

I think you have not shared the latest "CREATE TABLE" statement with me.

I suggest you to follow below step and share the result with me irrespective of what you have shared above.

1) Go to database in sql server
2) Expand tables
3) right click on table name (you mentioned as "class_emu_sector")
4) Goto "Script table as >> Create to >> clipboard
5) Paste the result here
6) Again right click on another table name ((you mentioned as "emu_avail")
7) Goto "Script table as >> Create to >> clipboard
8) Paste the result here
9) Run the query "SELECT * FROM class_emu_sector" .
10) Copy the result and paste here (Try to format your result in tabular format.
11) Run the query "SELECT * FROM emu_avail" .
12) Copy the result and paste here (Try to format your result in tabular format.

Thanks
Rahul D.

musiw 27-Jan-12 4:51am

USE [OMC]
GO
/****** Object: Table [dbo].[emu_avail] Script Date: 01/27/2012 17:42:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[emu_avail](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[id_sector] [int] NULL,
[trips] [int] NULL,
[total_trips] [int] NULL,
[emu_percent] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[total_emu_percent] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hybrid] [int] NULL,
[total_hybrid] [int] NULL,
[date] [datetime] NULL,
CONSTRAINT [PK_cancel_emu_avail] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [OMC]
GO
ALTER TABLE [dbo].[emu_avail] WITH CHECK ADD CONSTRAINT [FK__cancel_em__id_se__02084FDA] FOREIGN KEY([id_sector])
REFERENCES [dbo].[class_emu_sector] ([id_sector])
GO
ALTER TABLE [dbo].[emu_avail] WITH CHECK ADD CONSTRAINT [FK__cancel_em__id_se__208CD6FA] FOREIGN KEY([id_sector])
REFERENCES [dbo].[class_emu_sector] ([id_sector])
GO
ALTER TABLE [dbo].[emu_avail] WITH CHECK ADD CONSTRAINT [FK__emu_avail__id_se__282DF8C2] FOREIGN KEY([id_sector])
REFERENCES [dbo].[class_emu_sector] ([id_sector])
GO
ALTER TABLE [dbo].[emu_avail] WITH CHECK ADD FOREIGN KEY([id_sector])
REFERENCES [dbo].[class_emu_sector] ([id_sector])

musiw 27-Jan-12 4:52am

USE [OMC]
GO
/****** Object: Table [dbo].[class_emu_sector] Script Date: 01/27/2012 17:45:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[class_emu_sector](
[id_sector] [int] IDENTITY(1,1) NOT NULL,
[emu_sector] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_class_emu_sector] PRIMARY KEY CLUSTERED
(
[id_sector] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

musiw 27-Jan-12 4:58am

emu_avail:

5 1 110 NULL 15/13.64 NULL 0 NULL 2012-01-02 00:00:00.000
6 2 106 NULL 25/23.58 NULL 0 NULL 2012-01-02 00:00:00.000
7 3 37 NULL - NULL 0 NULL 2012-01-02 00:00:00.000
8 NULL NULL 253 NULL 40/15/81 NULL 0 2012-01-02 00:00:00.000

class_emu_sector:

1 Sg Gadut - Rawang - Sg Gadut
2 Bt. Caves - Pel. Klang - Bt. Caves
3 Rawang - Tg. Malim

RDBurmon 30-Jan-12 2:33am

Use this query

SELECT CES.emu_sector Sector ,EA.trips Trips,EA.emu_percent 'EMU/(%)' , EA.hybrid Hybrid
FROM emu_avail EA inner join class_emu_sector CES On EA.id_sector=CES.id_sector
Union all
SELECT 'Total' Sector , total_trips Trips,total_emu_percent 'EMU/(%)',total_hybrid Hybrid
FROM emu_avail WHERE id_sector is null

musiw 30-Jan-12 4:35am

thanks!! this code can be implement to other table which has total at below...

RDBurmon 30-Jan-12 4:49am

Correct . Please use it . And let me know if you stuck with any issue.

musiw 30-Jan-12 21:02pm

hi rahul,

can you help me on this.i really dont understand how to display this table and to build its database for this data.

this is hard copy report.do you have any idea how to implement it.or maybe make some modification/split table since the table is seems very complex..

class 81 82 83 Hybrid Avail @1500 1/1/2011
active 14 21 18 2 EMU Set Hybrid
avail. 2 5 15 2 22 2
Total as @0600 hrs 2/1/2011 22 2
KPI 40 2

fyi:
class:81,82,83 is EMU set
user key in available set for the day and the day before.

RDBurmon 31-Jan-12 2:27am

Sorry I didn't get you.All table values are messed up. Could you please format it as table and explain me again line by line

musiw 31-Jan-12 4:29am

sorry rahul..

this is the first table:

class 81 82 83 Hybrid
active 14 21 18 2
avail. 2 5 15 2
Total as
@0600 hrs 2/1/2011 22 2
KPI 40 2

the second table is:

Avail @1500 1/1/2012
Emu Set Hybrid
22 2

-> the second table is the data for the day before that user key in.
-> available emu set and hybrid @3 pm on 1/1/2012
-> 81,82,83,hybrid is class of EMU.
-> active is fix.it is total emu we have in service.
-> avail is how much available emu is for that day.
-> total as at 6 am on the day report is key in
-> KPI is some sort of target perfomance..and it is fix.

hope u can help me how to figure out this table.i dont understand how to create the databse for this kind of data.

thanks in advance.

RDBurmon 31-Jan-12 6:39am

Are this values ("@0600 hrs 2/1/2011 " , "@1500 1/1/2012") fixed ?
If not then how we are calculating this values ?

musiw 31-Jan-12 23:34pm

as far as i understand what user told me, its not fixed.user will key in the total available set that has been used from 3pm till 6am next day.

RDBurmon 1-Feb-12 13:44pm

Ok , I need some more information
1) Do you have any table which store values for "Class" ? Or do you want me to design it for you ?
2) Do you have any table which store values for "active" ? Or do you want me to design it for you ?
3) Do you have any table which store values for "avail" ? Or do you want me to design it for you ?
4) Do you have any table which store values for "Total as" ? Or do you want me to design it for you ?
5) Do you have any table which store values for "KPI" ? Or do you want me to design it for you ?
6) Do you have any table which store values for "Avail" (in second table) ? Or do you want me to design it for you ?
7) Do you have any table which store values for "EMU SET" ? Or do you want me to design it for you ?
8) Do you have any table which store values for "Hybrid" ? Or do you want me to design it for you ?

musiw 1-Feb-12 19:52pm

actually i had design it.halfway..but i dont really understand how the data will be stored.im not sure if the table is good enough to store the data and to display back later.i need your help..

RDBurmon 2-Feb-12 13:25pm

What does it mean ?
Total as
@0600 hrs 2/1/2011 22 2
KPI 40 2

what is meant by 22 and 2
what is meant by 40 and 2

Avail @1500 1/1/2012
Emu Set Hybrid
22 2

do you have any formula to calculate emuset like in above table it says that 22 emu set
do you have in formula to calculate hybrid ?

musiw 2-Feb-12 19:54pm

i guess we can create the formula.because it is the total of the available set on that day.(2+5+15=22).for the row KPI, i dont know how they identify it.maybe it is a index that they set to achieve perfomance on that day.they just set to 40.hybrid is only available for 2

for the 2nd table emu set is the total of available set (81,82,83) hybrid is 2.as far as i know hybrid is always 2.because there is only 2 hybrid coach.(active=2)

musiw 14-Feb-12 3:21am

hi rahul...

i just want to know if any idea from you regarding to this problem..im sorry for the uncovenience

RDBurmon 14-Feb-12 3:44am

Ok . Give me few hours I will provide my input on this