Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 07-11-2009
Member
 
Join Date: Jan 2009
Posts: 46
How to build cross references in a database

Hi,
I need some information on passing up a cross references query in a bunch of tables linked with each other. I need the same to view data in rows and columns type of table. The table consist of a series of row and columns which defines a category. The cross reference will help me to get a output for the proper query. I need a proper explanation on the same. If possible I need some examples with syntax for the same category. Thanks in advance.
Reply With Quote
  #2  
Old 07-11-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: How to build cross references in a database

Lets take a exmaple of a sample table. Like you have a list of product and the year they launched or earned good same. So the first column is product and the second is year. In the first one add the product list and the second the sale amount. Then to get pass a cross reference query use this syntax - TRANSFORM aggregate function SELECT PIVOT pivot field [IN (value1 [, value2 [, ...]])].
Reply With Quote
  #3  
Old 07-11-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: How to build cross references in a database

If we have a product table and another table order, we can see in total of orders per year for a given item, as displayed in the table above post. The syntax for this type of query is: TRANSFORM aggregate function SELECT PIVOT pivot field [IN (value1 [, value2 [, ...]])].
Where:
aggregate function - It's a SQL usually aggregate the functions that operates on the particular data.
select statement - It is a SELECT statement.
pivot field - It is the field or expression that you want to use to build the column headers in the query result.
value1, value2 fixed values are used to create the column headers.
To summarize data using a crosstab query, you will need to select the values of fields or expressions and as per the specified column headings, you can vew the data in more compact form as per the query.
Reply With Quote
  #4  
Old 07-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: How to build cross references in a database

TRANSFORM is optional but if included then it is the first statement in an SQL string. It precedes the SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally you can add other clauses like WHERE, that specifies an additional selection or sorting criteria. The values which are returned in pivot field are usually used as a column headings in the query result. For example, using the sales figures for the month of the sale as a pivot on a crosstab query would create 12 columns. Pivot field can restrict to create headings from fixed values (value1, value2) listed in the optional IN clause.
Reply With Quote
  #5  
Old 07-11-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: How to build cross references in a database

TRANSFORM clause indicates the value we want to see in columns that really belong to the query, in this case 1996 and 1997, since the other columns are optional. SELECT specifies the name of the optional columns we want to see, in this case Product, Code, Total and Media, giving the name of the field that we want to show in each column or the value thereof. If we include a calculation based on the result shall be based on data from the current row and not to the total data. FROM specifies the source of the data. The first table to be included is that of where we want to extract the data, this table must contain at least three fields, one for the titles of the line, some for column headers and one for calculating the value of the cells.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to build cross references in a database"
Thread Thread Starter Forum Replies Last Post
How to build a database driven website Kharbanda Software Development 4 18-05-2013 10:14 AM
Delete Printer References In the registry Victor Kam Windows XP Support 3 26-07-2011 02:13 PM
6950 Cross Fire vs 6870 Cross Fire Rainer Monitor & Video Cards 4 18-03-2011 07:08 AM
How to Return and Unset the References in PHP? Silent~Kid Software Development 4 23-02-2010 05:24 AM
What are the References in PHP? Flaco Software Development 5 23-02-2010 03:19 AM


All times are GMT +5.5. The time now is 07:47 PM.