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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 REwork query to show data correctly

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 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
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
) s
PIVOT
( MAX(counts) FOR strmacType IN ([Change USER],[Delete USER],[New USER]))p
Go to Top of Page

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 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.
Go to Top of Page

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.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]
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -