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