| Author |
 Topic  |
|
|
kdeutsch
Yak Posting Veteran
USA
75 Posts |
 Posted - 11/28/2012 : 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 Kount
John DOe Change User 31
John Doe Delete User 4
John Doe New User 3
Jane Doe Change User 1
Fred Doe Change User 4
Jack Doe Change User 3
Jack Doe New User 1
Mack Doe Change User 116
Would like to show this way instead
Name change User Delete User new User
John Doe 31 4 4
Fred Doe 4 0 0
Jack Doe 3 0 1
Here 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.intNCC
where uh.bitNCC = 1 Group by mn.strFullname, mt.strMacType |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/28/2012 : 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
) s
PIVOT
( MAX(counts) FOR strmacType IN ([Change USER],[Delete USER],[New USER]))p |
 |
|
|
kdeutsch
Yak Posting Veteran
USA
75 Posts |
Posted - 11/28/2012 : 14:44:37
|
hi, Ok I found a post online after i did this and came up with this query which works
Select NAME, [Change User], [Delete User], [New user] From (
Select distinct mn.strFullname NAME,
intUserId,
mt.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.intNCC) k
PIVOT
(Count(intUserId) for strMacType IN ([Change User], [Delete User], [New user])) as pvt
But 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
USA
75 Posts |
Posted - 11/28/2012 : 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.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.intNCC) k
PIVOT
(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
USA
75 Posts |
Posted - 11/29/2012 : 08:38:14
|
All,
Guess I do not know how to close topics on this forum as answered but this can be closed. |
|
|
| |
Topic |
|
|
|
REwork query to show data correctly
