Use SQL or ASP.NET programming

[Grey]

i have a database table as below

district sub_total purchase_date
loc1 20 2004/7/21
loc1 10 2004/7/31
loc1 5 2004/7/25
loc2 20 2004/7/23
loc2 10 2004/7/24
loc2 5 2004/7/25
loc3 20 2004/7/22
loc3 10 2004/7/30
loc3 5 2004/7/25


Result

District 21/7 22/7 23/7 24/7 25/7 26/7
Loc1 20 0 0 0 5 0
Loc2 0 0 20 10 5 0
Loc3 0 20 0 0 5 0

Can I use only SQL to select the result from the above table or I need to
use programming in ASP.NET to form the dataset???

Million thanks

 

[MattC]

Is that the results you have or want? What database are you on. If its
access look up the Transform and Pivot functions, if you are on SQL Server
then you will have to write your own pivot code.

MattC

 

[Scott Allen]

SQL 2005 has a PIVOT operator, but with SQL 2000 you'll have to do
your own pivoting. You can find some examples with a google search,
here is one:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp

HTH,