1

I have the following code i am tiring to get it to only show one line in the results for each major version, i need to combine all of the rows that are the same Like so.

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
,swc.MajorVersion
,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid


WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
GROUP BY
arp.DisplayName, arp.Publisher, swc.MajorVersion

order by
arp.DisplayName asc

Results :

Count   displayname MajorVersion

41  Adobe Reader 8  8

1   Adobe Reader 8.1.1  8

40  Adobe Reader 8.1.3  8

1   Adobe Reader 9.3    9

1   Adobe Reader 9.3.3  9

1   Adobe Reader 9.4.0  9

1   Adobe Reader 9.5.5  9

4   Adobe Reader X (10.1.13)    10

1   Adobe Reader X (10.1.4) 10

1   Adobe Reader X MUI  10

7   Adobe Reader XI 11

139 Adobe Reader XI (11.0.02)   11

1   Adobe Reader XI (11.0.03)   11

2   Adobe Reader XI (11.0.06)   11

28  Adobe Reader XI (11.0.07)   11

1   Adobe Reader XI (11.0.08)   11

62  Adobe Reader XI (11.0.09)   11

5527    Adobe Reader XI (11.0.10)   11

3   Adobe Reader XI (11.0.10)  MUI  11

2 Answers 2

1

try this: I removed a few fields and changed the group by

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
-- remove these fields
--,swc.MajorVersion
--,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid


WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
--change the group by
--GROUP BY
--arp.DisplayName, arp.Publisher, swc.MajorVersion

-- to this
group by arp.DisplayName


order by
arp.DisplayName asc
1

I didn't have a way to test this, but I went with a pivot of the display name and a dynamic column creation for other products and moved the cleaned up Inv_AddRemoveProgram table into a temp table:

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SELECT *
INTO #tmp
FROM Inv_AddRemoveProgram arp
WHERE arp.DisplayName NOT IN
        (
        'Power Scheme Plug-in Setup'
        ,'Altiris Inventory Agent'
        ,'Patch Management Agent'
        ,'Deployment Solution Agent'
        ,'Software Management Solution Agent'
        ,'Altiris Application Metering Agent'
        ,'Symantec pcAnywhere'
        ,'Symantec_pcAnywhere_plugin_installer'
        ,'Software Management Solution Plugin'
        )
    AND arp.DisplayName NOT LIKE 'Security Update%'
    AND arp.DisplayName NOT LIKE 'Update for%'
    AND arp.DisplayName NOT LIKE 'Hotfix for%'
    AND arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(arp.displayname) 
                    from Inv_AddRemoveProgram arp
                    group by arp.displayname
                    order by arp.displayname
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT MajorVersion, ' + @cols + ' 
FROM (SELECT
        arp.displayname
        ,swc.MajorVersion
        ,arp.Publisher

        FROM
        #tmp arp
        INNER JOIN
        vComputer vc
        ON arp.[_ResourceGuid] = vc.[Guid]
        inner join
        [Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid

        order by
        arp.DisplayName asc) AS SOURCE
PIVOT
(
    COUNT(DisplayName)
    FOR DisplayName IN (' + @cols + ')
) AS PIVOT;'

EXECUTE @query;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.