Query Help
-
I have three table
Computers - computername,computer, model, OS, ServiceTag, users
Software - Computername (fk to computers), softwarename
I gave my boss a spreadsheet listing all the users and the software they have - like this
SBauer Microsoft Excel
SBauer MicrosoftAccess
GSmith Microsoft Word
GSmith MicrosoftAccess
PJones Microsoft Excel
PJones Microsoft Word
PJones MicrosoftAccess
What he wanted was a list like this - I'm not sure how to pull the data vertically..
Username Microsoft Excel, MicrosoftAccess, MicrosoftWord
sbauer 1 1
GSmith 1 1
PJones 1 1 1
Any help would be greatly appreciated.
Thanks
Susan
-
That should be two tables, not three..
The other one isn't involved in the query
-
Select C.Computername, Count(s1.softwarename) as [Microsoft Excel], Count(s2.softwarename) as [MicrosoftAccess], ...
FROM Computers C
LEFT JOIN Software S1 ON C.Computername = S1.Computername AND S1.softwarename = 'Microsoft Excel'
LEFT JOIN Software S2 ON C.Computername = S2.Computername AND S2.softwarename = 'MicrosoftAccess'
....
GROUP BY C.Computername
_____________
Code for TallyGenerator -
You may try like this too..
SELECT [User] AS UserName
[MicrosoftExce] = CASE WHEN Max(SoftWare) = 'Microsoft Excel' THEN COUNT(Max(SoftWare)) ELSE 0 END,
[MicrosoftAccess] = CASE WHEN Max(SoftWare) = 'Microsoft Accessl' THEN COUNT(Max(SoftWare)) ELSE 0 END,
[MicrosoftWord] = CASE WHEN Max(SoftWare) = 'Microsoft Wordl' THEN COUNT(Max(SoftWare)) ELSE 0 END,
FROM tbl
GROUP BY [User]
-
This will work if to make it this way:
[Microsoft Excel] = COUNT(CASE WHEN SoftWare = 'Microsoft Excel' THEN SoftWare ELSE NULL END),
same for others.
_____________
Code for TallyGenerator -
It does not work that way!!
[Microsoft Excel] = COUNT(CASE WHEN SoftWare = 'Microsoft Excel' THEN SoftWare ELSE NULL END),
This one might work for you
SET NOCOUNT ON
DECLARE @tbl TABLE (CUser VARCHAR(10),Software VARCHAR(30))
INSERT INTO @tbl SELECT 'SBauer','Microsoft Excel'
INSERT INTO @tbl SELECT 'SBauer','Microsoft Access'
INSERT INTO @tbl SELECT 'GSmith','Microsoft Word'
INSERT INTO @tbl SELECT 'GSmith','Microsoft Access'
INSERT INTO @tbl SELECT 'PJones','Microsoft Excel'
INSERT INTO @tbl SELECT 'PJones','Microsoft Word'
INSERT INTO @tbl SELECT 'PJones','Microsoft Access'
SELECT UserName,
MicrosoftExcel = SUM(MicrosoftExcel),
MicrosoftAccess = SUM(MicrosoftAccess),
MicrosoftWord = SUM(MicrosoftWord)
FROM (
SELECT [CUser] AS UserName,
[MicrosoftExcel] = CASE WHEN SoftWare = 'Microsoft Excel' THEN 1 ELSE 0 END,
[MicrosoftAccess] = CASE WHEN SoftWare = 'Microsoft Access' THEN 1 ELSE 0 END,
[MicrosoftWord] = CASE WHEN SoftWare = 'Microsoft Word' THEN 1 ELSE 0 END
FROM @tbl
GROUP BY [CUser],SoftWare
 
DerivedGROUP BY Derived.UserName
Ram
-
Did you try?
This works perfectly:
DECLARE @tbl TABLE (CUser VARCHAR(10),Software VARCHAR(30))
INSERT INTO @tbl SELECT 'SBauer','Microsoft Excel'
INSERT INTO @tbl SELECT 'SBauer','Microsoft Access'
INSERT INTO @tbl SELECT 'GSmith','Microsoft Word'
INSERT INTO @tbl SELECT 'GSmith','Microsoft Access'
INSERT INTO @tbl SELECT 'PJones','Microsoft Excel'
INSERT INTO @tbl SELECT 'PJones','Microsoft Word'
INSERT INTO @tbl SELECT 'PJones','Microsoft Access'
SELECT CUser,
COUNT(CASE WHEN SoftWare = 'Microsoft Excel' THEN SoftWare ELSE NULL END) as [Microsoft Excel],
COUNT(CASE WHEN SoftWare = 'Microsoft Access' THEN SoftWare ELSE NULL END) as [Microsoft Access],
COUNT(CASE WHEN SoftWare = 'Microsoft Word' THEN SoftWare ELSE NULL END) as [Microsoft Word]
FROM @tbl
GROUP BY CUser
Returned result:
CUser Microsoft Excel Microsoft Access Microsoft Word
---------- --------------- ---------------- --------------
GSmith 0 1 1
PJones 1 1 1
SBauer 1 1 0
_____________
Code for TallyGenerator -
u can use the pivot operator if u r using sql 2005.
u may have to use some dynamic queries but i would like to think that this would be simpler.
"Keep Trying"
-
"That should be two tables, not three.."
Actually I think it should be 3 tables. One for computers, one for software, and one that says which SW is on which computer. The way you have it, you are repeating the SW name over and over and you can have typos like "Microsoft Ecxel" which would then give you some headaches. In any of the proposed solutions, if you have this typo, you will lose the information in result table.
To avoid this, I would add a check for unknown values. Using Sergiy's COUNT(CASE ...) approach - which I like - it would be:
SELECT CUser,
COUNT(CASE WHEN SoftWare = 'Microsoft Excel' THEN SoftWare ELSE NULL END) as [Microsoft Excel],
COUNT(CASE WHEN SoftWare = 'Microsoft Access' THEN SoftWare ELSE NULL END) as [Microsoft Access],
COUNT(CASE WHEN SoftWare = 'Microsoft Word' THEN SoftWare ELSE NULL END) as [Microsoft Word],
COUNT(CASE WHEN SoftWare NOT IN ('Microsoft Word','Microsoft Access','Microsoft Excel') THEN 'check data' ELSE NULL END) as [Unknown]
FROM @tbl
GROUP BY CUser
Any time the column Unknown has any values, it means that either there is a typo, or a new SW was added that you don't have in the query yet. Of course you don't have to include this column when sending data to your boss, but it is nice to have a check in place.
-
I just love it when they don't
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply