how to format

[arravalli12]

In my asp page, I have this query:

select name, count(name)as cnt from WineClubSubscriptions where status = 'w' group by name order by name

and this is dispalyed in table as name and quantity.

Wine Club Discovery Mixed Every Other Month 107
Wine Club Discovery Mixed Monthly 155
Wine Club Discovery Red Every Other Month 57
Wine Club Discovery Red Monthly 86
Wine Club Discovery White Every Other Month 12
Wine Club Discovery White Monthly 12
Wine Club Platinum Mixed Every Other Month 28
Wine Club Platinum Mixed Monthly 47
Wine Club Platinum Red Every Other Month 23
Wine Club Platinum Red Monthly 63
Wine Club Platinum White Monthly 7
Wine Club Silver Mixed Every Other Month 48
Wine Club Silver Mixed Monthly 104
Wine Club Silver Red Every Other Month 36
Wine Club Silver Red Monthly 77
Wine Club Silver White Every Other Month 4
Wine Club Silver White Monthly 6

I have been asked by the user to split and show in the following format:

Name mixed red white total

discovery every
other month

Discovery monthly

Discover month total

silver every
other month

silver monthly

silver month total

I will appreciate if any one Can give me idea how to do this...
Thanks

 

[DotNetGnat]

can you show us your table and some sample data in the database...i mean i was wondering if you had separate fields that tell you whether the record is mixed or red or...and whether it is silver month or the other month...

if you have these fields then it would be a simple CASE statement that need to be used inside in the sql query to get the desired output...

-DNG

 

[arravalli12]

I do not have separate fields in my table to show they are mixed or red or etc. In that case it would have been little simpler. Here I am trying to hold them into variable using like operator but then the program is becoming very longer and not showing what I want to display.
Any iideas will be appreciated..

 

[DotNetGnat]

then i would say there wont be any elegant way to handle this situation...and i cant think of anything after looking at the values of the Name field in your database...

-DNG