ASP & SQL problem to split values into separate columns

[mikevde]

Hi,

I have a query where I select all the values from my MS SQL table that are LIKE a certain string. The query works, but in the ASP page, I only want to write the date once, while writing the values from the other column in all cases.

To illustrate, i have a table that looks like this:

ID Date Name
1 2007/01/01 city_house
2 2007/01/02 city_farm
3 2007/01/02 city_house
4 2007/01/02 city_house
5 2007/01/02 city_farm


my sql is basically

SELECT COUNT(*) AS countcity, date, name
WHERE Name LIKE '%City%') AND (YEAR(Date) = '2007')
GROUP BY b.Name, a.Date
ORDER BY a.ChatDate

The query returns as expected all the data that matvches that criteria, i.e

countcity date name
1 2007/01/01 city_house
2 2007/01/02 city_farm
2 2007/01/02 city_house

etc.

The desired outcome of my ASP page is that I can use the query to get the Name column split into one for city_house and one for city_farm.


Date City_House City_Farm
2007/01/01 1 0
2007/01/02 2 2

How can I achieve this with ASP? Is there a better SQL query to use to achive this?

Thanks

[jhardman]

I can't think of a different sql query to get that result. In asp I usually handle this by setting a variable equal to the date and checking each entry against the variable. If the entry has the value already stored in the variable, I don't print the date. If the entry has a different value, I print the date, and update the variable before I go on. Does this make sense?

Jared

[mikevde]

Can you help me with that ASP code? That is the part I am struggling with really.
Thanks

[danp129]

With this data in table "rentals":

ID Date Name
1 2007/01/01 city_house
2 2007/01/02 city_farm
3 2007/01/02 city_house
4 2007/01/02 city_house
5 2007/01/02 city_farm

Using this Query

SELECT b.date, sum(case when b.name like '%house%' then 1 else 0 end) as City_House, sum(case when b.name like '%farm%' then 1 else 0 end) as City_Farm
from rentals b
WHERE Name LIKE '%City%' AND (YEAR(Date) = '2007')
GROUP BY b.Date
 

Will return this recordset

Date City_House City_Farm
2007-01-01 00:00:00 1 0
2007-01-02 00:00:00 2 2

[mikevde]

thanks that works perfectly now!

M