SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 REwork query to show data correctly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 11/28/2012 :  14:11:03  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/28/2012 :  14:26:35  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 11/28/2012 :  14:44:37  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 11/28/2012 :  14:56:42  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 11/29/2012 :  08:38:14  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000