Cross Tab Query

Hi!

I m using sql 2005.I have a table as showing below.

yearRegionloan_amtpur_idpurpose1981Andhra pradesh$20,000.00 1Animal Husbandary1981Arunachal Pradesh$110,000.00 1Animal Husbandary1981Assam$240,000.00 1Animal Husbandary1981Bihar$75,000.00 1Animal Husbandary1981Chhattisgarh$55,500.00 1Animal Husbandary1981Gujarat$77,500.00 1Animal Husbandary1982Goa$44,888.00 1Animal Husbandary1982Himachal pradesh$4,000.00 1Animal Husbandary1982Himachal pradesh$20,000.00 1Animal Husbandary1982Jammu and kashmir$30,000.00 1Animal Husbandary1882Jharkhand$35,000.00 1Animal Husbandary1982Karnataka$40,000.00 1Animal Husbandary1982Kerala $20,000.00 1Animal Husbandary1982Madhya pradesh$5,000.00 1Animal Husbandary

I want to produce report as by using crosstab query as showing bellow

Year Asam Hyadrabad goa arunachal pardesh etc.........

1981 1000.00 2000.00 8000.2 00000 000000 .....

1981

'

'

Is it possible by crosstab query ?or please suggest me another way as early as possible.

Thanx in advance.

Abhishek

Have a look at thePIVOT method in SQL Server 2005.

|||

Agree.

Based on my understanding, I think you can use crosstab query in your case. It is desgined for that.

A crosstab calculates an aggregation on a certain column (A) in a set of rows. It then transforms the unique values in another column (X) into column names for the result. All other columns (Y) are used for grouping and their unique values define the rows for the result. The aggregated values of A are distributed among columns in the result according to the value of X and among rows in the result according to the value of Y.

BTW, since you are using sql2005, i think you would need this link:http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

You can first run that script to create a sp_crosstab then you will be able to use cross tab through your database. thanks

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

|||

can you be more specific about the columns in the query? I think it can be dne with Group By.

|||

You can't do this by cross-matrix query.

But you can gather such data Datatable in asp.net.

say you have three tables

table a which contains columns : year_id, year.

table b which contains columns : region_id, region.

table c which contains columns : year_id, region_id, amt.

you just do:

string sql="select year_id, year from a";

datatable maindt=sqlhelper.executedatatable(cn, text, sql);

sql="select region_id, region from b";

datatable tempdt=sqlhelper.executedatatable(cn, text, sql);

foreach (datarow dr in tempdt.rows)

{

maindt.columns.add(dr["region"].tostring());

}

foreach (datarow dr in maindt.rows)

{

for(int i=2;i<maindt.columns.count;i++)

{

sql="select amt from c, b where c.region_id=b.region_id and year_id=dr["year_id"].tostring() and region=maindt.columns[i].columnname";

tempdt=sqlhelper.executedatatable(cn, text, sql);

dr[i]=tempdt.rows[0][0].tostring();

}

}

|||

mayankparmar2000:

You can't do this by cross-matrix query.

Yes you can (see the link in my first post). There are other methods as well if you are using a version of SQL Server older than 2005.

|||

Hi guies !

Finaly i resolve this problem by using Pivote in sql 2005 .

Any way thanks for all of u for ur suggestion.

Abhishek.