使用SQL Server表值的组合在ASPX页面上以矩阵形式显示数据 [英] Showing data in matrix form on aspx page using combination of sql server table values

问题描述

大家好，

我正在使用VS 2008/2010和SQL Server2008.我有3个表:

1)基金
2)ShareClass
3)ShareClassCountry

表的脚本是:

Hi All,

I am using VS 2008/2010 and SQL server 2008. I have 3tables :

1) Fund
2) ShareClass
3) ShareClassCountry

The script of table are :

CREATE TABLE [dbo].[Fund](
	[ID] [int] NOT NULL,
	[BaseCurrency] [nvarchar](50) NOT NULL,
	[Benchmark] [text] NULL,
	[DomicileCountry] [nvarchar](255) NOT NULL,
	[Fundcode] [nvarchar](50) NOT NULL,
	[FundType] [nvarchar](50) NOT NULL,
	[LaunchDate] [datetime] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[TargetAlpha] [nvarchar](255) NULL,
	[UmbrellaFund] [nvarchar](255) NOT NULL,
	[FundID] [int] IDENTITY(1,1) NOT NULL,
	[InvestmentStrategyID] [int] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Modified] [datetime] NOT NULL,
	[Deleted] [datetime] NULL,
	[Liquidity] [nvarchar](255) NULL,
	[ShortName] [nvarchar](200) NULL,
	[URLName] [nvarchar](200) NULL
	)
	GO
	CREATE TABLE [dbo].[ShareClass](
	[ID] [int] NOT NULL,
	[Bloomberg] [nvarchar](50) NULL,
	[ShareClassID] [int] IDENTITY(1,1) NOT NULL,
	[ISIN] [nvarchar](50) NULL,
	[LocalCurrency] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Status] [bit] NULL,
	[FundID] [int] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Modified] [datetime] NOT NULL,
	[Deleted] [datetime] NULL,
	[PricingFrequency] [varchar](50) NULL
	)
	GO
	CREATE TABLE [dbo].[ShareClassCountries](
	[ShareClassID] [int] NOT NULL,
	[CountryCode] [nvarchar](10) NOT NULL,
	[Language] [nvarchar](10) NOT NULL,
	[CountryName] [nvarchar](50) NOT NULL,
	[FileReceived] [bit] NOT NULL,
	[FileActive] [bit] NOT NULL,
	[NewFileActiveDate] [datetime] NULL,
	[DateLastRequested] [datetime] NULL,
	[DateDeleted] [datetime] NULL,
	[FileSize] [varchar](10) NULL,
	[DateReceived] [datetime] NULL
	)

我需要使用这3个表以矩阵形式显示数据，如下所示:

基金名称(行标题)->它只会是一行，列出基金表中的所有基金名称

ShareclassName (第1列)，->这将是来自"Shareclass"表的shareclass名称.当多个国家使用单个共享类时，将重复使用这些名称.

CountryCode (第2列)->它来自ShareClassCountries表.国家/地区代码直接与shareclassId对应，因此共享类"A"可以具有3个国家/地区代码CHF，EUR，GBP，共享类"B"可以具有相同的国家/地区代码加上更多或更少的国家代码

FileActive 行值✓如果值为1，则为X，如果值为0

I need to display the data using these 3 tables in matrix form as:

Fund Name(Row header) --> It would just be a single row listing all the fund names from fund table

ShareclassName(Column 1), --> It would be shareclass name which would come from ''Shareclass'' table. These names will be repeated as a single shareclass is used in multiple countries.

CountryCode (Column 2) --> It would come from ShareClassCountries table. Country code directly corresponds to the shareclassId so a shareclass ''A'' can have 3 country code CHF,EUR,GBP and shareclass ''B'' can have same country code plus more country code or less

FileActive Row value ✓ if the value is 1 and X if value is 0

    Fund 1	Fund 2	Fund 3	Fund 4
           		F1	F2	F3	F4
Shareclass1	country1 ✓		✓	✓	✓	✓
	   country2	 ✓	✓	✓	✓
	country3	✓	✓	✓	✓
	country4	✓	✓	✓	✓
Shareclass2	country1	✓	✓	✓	✓
	country2	✓	✓	✓	✓
Shareclass3	country1	✓	✓	✓	✓
	country1	✓	✓	✓	✓

所有这三个表都有关系，我想使用这3个表以aspx页上的矩阵格式显示数据.我已经使用了Repeater，gridivew等，但是我没有使用这些控件中的任何一个在matix中显示数据.

我在Google上搜索过，有人建议使用数据透视表，但到目前为止，我不知道该如何使用...

请让我知道如何实现..

感谢帮助..

All the three tables have relation and I want to show the data using these 3 tables in the matrix format on aspx page. I have used repeater, gridivew etc but i have not showed the data in matix using any of these control.

I have searched on google and some suggests to use pivot table but as of now i have no idea how can that be used...

Please let me know how this can be achieved..

Help appreciated..

推荐答案

你好AngelVarun，

我的文章 [

Hello AngelVarun,

my article[^] explains it all.

Use datatable ,
Create you own datatable and fill it by doing logic on data which are selected from database

请选中此选项:

Check this one please:

select f.Name as FundName, sc.Name as ShareClass, scc.CountryCode, scc.FileActive as FileActive
into #tempPivotTable
from Fund f
LEFT OUTER JOIN ShareClass as sc on sc.FundID = f.FundID
LEFT OUTER JOIN ShareClassCountry as scc on scc.ShareClassID = sc.ShareClassID
--GROUP BY f.Name as FundName, sc.Name as ShareClass, scc.CountryCode

DECLARE @columns VARCHAR(8000)

SELECT @columns = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + cast([FundName] as varchar)
                        FROM    #tempPivotTable AS t2
                        ORDER BY '],[' + cast([FundName] as varchar)
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM #tempPivotTable
PIVOT
(
MAX(FileActive)
FOR [FundName]
IN (' + @columns + ')
)
AS p '

EXECUTE(@query)

drop table #tempPivotTable

这篇关于使用SQL Server表值的组合在ASPX页面上以矩阵形式显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！