| Author |
Topic |
|
Astaroth
Starting Member
9 Posts |
 Posted - 2007-06-09 : 11:11:41
|
| AfternoonI am trying to write a query that will return the columns: year/ month, each status type (unknown how many types there are)Each row is a different join year/ monthEach cell has the count of users that joined in that rows year/ month and currently have the status of the column.At the moment I have the following query:SELECT [remortgage-status].status, COUNT(1) AS CountTotal, YEAR([remortgage-log].datetime) AS Year, MONTH([remortgage-log].datetime) AS month FROM [remortgage-status] INNER JOIN [remortgage-log] ON [remortgage-status].clientid = [remortgage-log].clientid WHERE ([remortgage-log].action = N'Joined') GROUP BY [remortgage-status].status, YEAR([remortgage-log].datetime), MONTH([remortgage-log].datetime)The problem is that each different status is a new row rather than each status being a column.What do I need to do to correct this? - I dont know all the different possible statuses at this point |
|
|
Astaroth
Starting Member
9 Posts |
Posted - 2007-06-09 : 11:15:40
|
| PS. How do I join the month and year in the return?Many thanks |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-09 : 19:21:05
|
what do you mean by join the month and year ?
KH |
|
|
|
Astaroth
Starting Member
9 Posts |
Posted - 2007-06-10 : 02:10:32
|
| I meant so that I got a single column like Jun-07 rather than a column for the year (eg 2007) and a second column for the month (eg June) - this bit however I have worked out.The larger problems of getting columns rather than rows is still beyond me though. Have a working solution for now of messing with the response from the sql in the program to fill a new datatable and then displaying this datatable but I am sure this must be less efficient than getting the SQL response back correctly in the first place. |
|
|
|
pbguy
Constraint Violating Yak Guru
319 Posts |
Posted - 2007-06-10 : 23:36:47
|
| In order to combine more than one column to a single column, you have to use '+' to concatenate the columns in the select list.Note : If a column is other than string column(char, varchar), column should be type casted.post sample data and output you want.--------------------------------------------------S.Ahamed |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-10 : 23:42:52
|
quote:
Originally posted by Astaroth
I meant so that I got a single column like Jun-07 rather than a column for the year (eg 2007) and a second column for the month (eg June) - this bit however I have worked out.The larger problems of getting columns rather than rows is still beyond me though. Have a working solution for now of messing with the response from the sql in the program to fill a new datatable and then displaying this datatable but I am sure this must be less efficient than getting the SQL response back correctly in the first place.
You mean pivoting ? If you are using any reporting tools to show the result, it will be easier to do it there.For doing PIVOTing in SQL, search for PIVOT in this site.
KH |
|
|
|
Astaroth
Starting Member
9 Posts |
Posted - 2007-06-11 : 02:04:48
|
| It does feel like pivot tables from excel come to think of it, but i dont understand them in excel either :)Will do a search and then guestimate if my asp.net or sql code will be more efficient |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-11 : 02:10:08
|
Or post your table structure, sample data and the result that you want
KH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Astaroth
Starting Member
9 Posts |
Posted - 2007-06-11 : 10:03:10
|
| Thanks for that... looks like my .net is here to stay then. |
|
|
|
|
rows to columns