问按主要版本列出软件的SQL查询

EN

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

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;