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 |
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2012-11-28 : 14:11:03
|
What I am trying to do is how the data from query a different way, current query return like so.name macType KountJohn DOe Change User 31John Doe Delete User 4John Doe New User 3Jane Doe Change User 1Fred Doe Change User 4Jack Doe Change User 3Jack Doe New User 1Mack Doe Change User 116Would like to show this way insteadName change User Delete User new UserJohn Doe 31 4 4Fred Doe 4 0 0Jack Doe 3 0 1Here is the current way i am doing this.Select distinct mn.strFullname, mt.strmacType, COUNT(strMacType)from iMAC_UsersHist as uh LEFT JOIN iMAC_MacType as mt on mt.intMacTypeID = uh.intMacTypeId Left Join tblPersonnel as mn on mn.intPersonnelId = uh.intNCCwhere uh.bitNCC = 1 Group by mn.strFullname, mt.strMacType |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-28 : 14:26:35
|
You should be able to use a pivot operator over your existing query as shown below(changes in red) SELECT * FROM ( SELECT DISTINCT mn.strFullname, mt.strmacType, COUNT(strMacType) counts FROM iMAC_UsersHist AS uh LEFT JOIN iMAC_MacType AS mt ON mt.intMacTypeID = uh.intMacTypeId LEFT JOIN tblPersonnel AS mn ON mn.intPersonnelId = uh.intNCC WHERE uh.bitNCC = 1 GROUP BY mn.strFullname, mt.strMacType) sPIVOT( MAX(counts) FOR strmacType IN ([Change USER],[Delete USER],[New USER]))p |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2012-11-28 : 14:44:37
|
hi, Ok I found a post online after i did this and came up with this query which worksSelect NAME, [Change User], [Delete User], [New user] From (Select distinct mn.strFullname NAME, intUserId, mt.strmacTypefrom iMAC_UsersHist as uh LEFT JOIN iMAC_MacType as mt on mt.intMacTypeID = uh.intMacTypeId Left Join tblPersonnel as mn on mn.intPersonnelId = uh.intNCC) kPIVOT (Count(intUserId) for strMacType IN ([Change User], [Delete User], [New user])) as pvtBut what I have not found oout a way is to total on the end and total at the bottom, is there a way with pivot command.Thanks For the help. |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2012-11-28 : 14:56:42
|
Hi,Ok Summed up hte back end of it all but don't know how to get the bottom totals.Select NAME, [Change User], [Delete User], [New user], SUM([Change User]) + SUM( [Delete User]) + SUM( [New user]) Total From (Select distinct mn.strFullname NAME, intUserId, mt.strmacTypefrom iMAC_UsersHist as uh LEFT JOIN iMAC_MacType as mt on mt.intMacTypeID = uh.intMacTypeId Left Join tblPersonnel as mn on mn.intPersonnelId = uh.intNCC) kPIVOT(Count(intUserId) for strMacType IN ([Change User], [Delete User], [New user])) as pvt group by NAME, [Change User], [Delete User], [New user] |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2012-11-29 : 08:38:14
|
All,Guess I do not know how to close topics on this forum as answered but this can be closed. |
|
|
|
|
|
|
|