Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

excel like table

hello

I have a spreadsheet with a matrix style table of data, and just wondering how best display and update in asp.net
here is a sample of the data:

                MNYC      MLON      RLON
MNYC        65        220        300
MLON       220        45         180
RLON        300        180        200
UK            300        200         200

the column and row headings are area codes and I have around 30 of them

I will store in SQL with a data structure of
from_area   to_area    value
MNYC          MNYC       65
MNYC          MLON       220

so I need to build the table and allow the values to be editted.

thanks for any help.
0
Graham_Forbes
Asked:
Graham_Forbes
1 Solution
 
Alan WarrenCommented:
Hi Graham,
This will get you started in the right direction.

If you normalise your data, you should be able to use Using PIVOT and UNPIVOT to present your data in the cross-tab fashion you are looking for.

You could then create a sqldatasource binding the SQL to a DataGrids (DatasourceID), you would need to provide a selectcommand like this in order to view the data in a datagrid.
-- Pivot table with four rows and four columns
SELECT from_area as AREA , [1] as MLON, [2] as MNYC, [3] as RLON, [4] as UK
FROM 
(SELECT t2.[Area_Code] as from_area, t.to_area, t.value FROM Area_Mapping t inner join Area_Code t2 on t.from_area=t2.id ) p
PIVOT
(
avg([value])
FOR [to_area] IN( [1], [2], [3], [4])) AS pvt

ORDER BY pvt.from_area;

-- Returns
-- AREA	MLON	MNYC	RLON	UK
-- MLON	45	220	180	NULL
-- MNYC	220	65	300	NULL
-- RLON	180	300	200	NULL
-- UK	200	300	200	NULL

Open in new window


Create Area_Code table:
/****** Object:  Table [dbo].[Area_Code]    Script Date: 09/14/2012 07:39:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Area_Code](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Area_Code] [varchar](5) NULL,
 CONSTRAINT [PK_Q_27863858_Area] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window


Create Area_Mapping table:
/****** Object:  Table [dbo].[Area_Mapping]    Script Date: 09/14/2012 07:40:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Area_Mapping](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[from_area] [tinyint] NULL,
	[to_area] [tinyint] NULL,
	[value] [int] NULL,
 CONSTRAINT [PK_Q_27863858] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window


Sample data (area_code table):
ID	Area_Code
1	MLON
2	MNYC
3	RLON
4	UK

Open in new window


Sample data (area_mapping table):
ID	from_area	to_area	value
1	2	2	65
2	2	1	220
3	2	3	300
4	1	2	220
5	1	1	45
6	1	3	180
7	3	2	300
8	3	1	180
9	3	3	200
10	4	2	300
11	4	1	200
12	4	3	200

Open in new window

Alan
0
 
Graham_ForbesAuthor Commented:
Thanks for this, I can see how a pivot would work to display, any thoughts on how to update ? Could it be done in a table, or should I embed the from and to in the table and if you click to edit it jumps to a new page?
0
 
Amandeep Singh BhullarCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now