Showing data in matrix form on aspx page using combination of sql server table values

Question

5.00/5 (1 vote)

See more:

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 :

SQL

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
	)

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

HTML

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

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..

Posted 14-Mar-12 0:16am

angelvarun

Updated 15-Mar-12 4:33am

v3

Add a Solution

3 solutions

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)

Herman<T>.Instance · Answer 1 · 2012-03-14T00:25:00

Solution 1

Hello AngelVarun,

my article[^] explains it all.

Posted 14-Mar-12 0:25am

Herman<T>.Instance

Comments

angelvarun 14-Mar-12 6:48am

Thanks digimanus. i had a look into the queries. i was looking for some queries using multiple tables like
select t1.column1, t2.column from table t1, table2 ...

and i could not see multiple tables in your query, as you know i want the matrix /pivot using multiple table, do you have any such handy query. I am not a sql expert so felt bit difficult to modify your query using one table to make it work for me.

Herman<T>.Instance 14-Mar-12 8:18am

The first part befor the pivotting in my article is the basic data collection. In that part you can use the multple tables. Show your query and I help you out.

angelvarun 14-Mar-12 8:53am

I will need to first make a simple query of what data i want and then i would give you that result set and the query. So you would be able to work only after that. I will get back to you shortly. Thanks mate..

angelvarun 15-Mar-12 10:31am

Hi digimanus,
I have modified my question and now it should be easy for you to help me with the query. Can you please help me out?

Herman<T>.Instance 15-Mar-12 10:53am

this could be the query to run (please tell me if it works for you):
select f.Name as FundName, sc.Name as ShareClass, scc.CountryCode, COUNT(scc.CountryCode) as nrCounted
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(nrCounted)
FOR [FundName]
IN (' + @columns + ')
)
AS p '

EXECUTE(@query)

drop table #tempPivotTable

Herman<T>.Instance 15-Mar-12 11:06am

small question: how many funds do you have?

angelvarun 15-Mar-12 13:23pm

There are 34 funds and i am getting only 16 fund columns...

Herman<T>.Instance 16-Mar-12 4:41am

that is possible because not all funds are used. If the match between the data does not exist than it is not seen in the report.

angelvarun 16-Mar-12 6:24am

You are right digimanus, i figured that out today morning, but currently i m trying to figure out for the duplicate rows coming...

angelvarun 15-Mar-12 11:28am

Thanks a lot digimanus. I can see the format of result i want, but i am able to see the duplicate rows, i will try to modify ur given query and will get back to you once i feel helpless. Thanks again

angelvarun 16-Mar-12 7:57am

Hi digimanus,
Can you please modify the query to make use of the 'FileActive' column from the table 'ShareClassCountries' and display '1' if FileActive is 1 else display '0' in the result, currently i can see that you are using count of countrycode and show that count in the result. Can you please do this favour...

Sudip Saha · Answer 2 · 2012-03-14T00:27:00

Solution 2

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

Posted 14-Mar-12 0:27am

Sudip Saha

Comments

angelvarun 14-Mar-12 6:52am

Hi Sudip,

Thanks for your suggestion. I wanted the logic of pivot to use multiple table. Are you referrring to datatable object of .net? If so then i have the idea that i can create customised datatable by different sql queries like in ds[0].tables[0] ,ds[0].tables[1] etc but it would be very complex one and bit old.

Herman<T>.Instance · Answer 3 · 2012-03-16T04:59:00

Check this one please:

SQL

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