Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server
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
	)
 
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	✓	✓	✓	✓
 

 

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 1:16am
Edited 15-Mar-12 5:33am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hello AngelVarun,
 
my article[^] explains it all.
  Permalink  
Comments
angelvarun at 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.
digimanus at 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 at 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 at 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?
digimanus at 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
 
digimanus at 15-Mar-12 11:06am
   
small question: how many funds do you have?
angelvarun at 15-Mar-12 13:23pm
   
There are 34 funds and i am getting only 16 fund columns...
digimanus at 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 at 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 at 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 at 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...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Use datatable ,
Create you own datatable and fill it by doing logic on data which are selected from database
  Permalink  
Comments
angelvarun at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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
  Permalink  
Comments
angelvarun at 19-Mar-12 6:14am
   
Hi digimanus,
I am getting the error :
 
(7155 row(s) affected)
Msg 8117, Level 16, State 1, Line 2
Operand data type bit is invalid for max operator.
 
I feel the error is because you are using Max on the column 'FileActive' and the data type of the column is bit.
 
Let me know on this please.
digimanus at 20-Mar-12 16:57pm
   
you are right, should be MAX(Cast(FileActive as int))

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

  Print Answers RSS
0 George Jonsson 175
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 16 Mar 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100