Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-01-16 : 09:02:57
|
hello all,i have table in which i need to get data year wise like 2009,2010,2011,2012i written this query SELECT UserCN,LastName,FirstName,Gender,DOB,TotalPointsCoeff,Level FROM EXCZ_WellcentPointTotal ewjoin USR_Demographics ud ON ew.UserID=ud.UserIDWHERE ew.UserID IN(SELECT DISTINCT ud.UserID FROM USR_Demographics udjoin EXCZ_measurements lu on ud.UserID=lu.UserIDWHERE ud.CustId=277 AND year(entryDate)=2009INTERSECTSELECT DISTINCT ud.UserID FROM USR_Demographics udjoin EXCZ_measurements lu on ud.UserID=lu.UserIDWHERE ud.CustId=277 AND year(entryDate)=2010it is showing in column wise...again i used pivot to get rquired result select UserCN,LastName,FirstName,Gender,DOB,TotalPointsCoeff,Level from ( select Year(entryDate) as pivot_col FROM EXCZ_WellcentPointTotal ew INNER JOIN USR_Demographics as o ON (ew.UserID=o.UserID) join EXCZ_measurements lu on o.UserID=lu.UserIDWHERE o.CustId=277 ) as t pivot ( COUNT(entryDate for pivot_col in ([2009],[2010],[2011],[2012]) ) as pit not showing any result how to do this one??P.V.P.MOhan |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-16 : 09:37:00
|
[code]select Year(entryDate) as pivot_col, EntryDate FROM EXCZ_WellcentPointTotal ew INNER JOIN USR_Demographics as o ON (ew.UserID=o.UserID) join EXCZ_measurements lu on o.UserID=lu.UserIDWHERE o.CustId=277 ) as t pivot ( COUNT(entryDate) for pivot_col in ([2009],[2010],[2011],[2012]) ) as p[/code]--Chandu |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|