How to use wildcard with SQL pivot

Question

In SCCM, I am creating a pivot report that will bring back application versions from a list of applications. This is working well for me using the following code, as long as I know the exact name of the installed application:

SELECT netbios_name0  AS 'Computer Name', 
       model0         AS Model, 
       [Google Chrome], 
       [Zscaler],
       [itunes],
       [Microsoft Office 365 ProPlus - en-us] AS 'Office'
              
FROM   (SELECT rSYS.netbios_name0, 
               CS.model0, 
               arp.displayname0, 
               arp.version0 AS ARPVersion 
        FROM   v_r_system rSYS 
               JOIN v_gs_computer_system CS 
                 ON CS.resourceid = rsys.resourceid 
               JOIN v_add_remove_programs arp 
                 ON rsys.resourceid = arp.resourceid 
               
       ) Temp 
       PIVOT ( Max(arpversion) 
             FOR displayname0 IN ([Google Chrome], 
                                  [Zscaler],
                                  [itunes],
                                  [Microsoft Office 365 ProPlus - en-us]
                                    ) ) Piv 

The query results in the following:

ComputerName    Model                  Google Chrome    Zscaler     itunes      Office
Computer1       Latitude 5300 2-in-1   86.0.4240.111    2.1.2.105   12.9.2.6    16.0.11328.20368
Computer2       Latitude 7390          86.0.4240.111    2.1.2.105   12.9.2.6    16.0.11328.20492
Computer3       Latitude 7390 2-in-1   86.0.4240.111    1.5.2.501   12.9.2.7    16.0.11328.20492

This is exactly the way I want the report to look, however, many/most applications have names that change with each version, so I need to be able to do a wildcard search for the different versions of an application. I have tried using the % wildcard in my IN statement, but it looks like SQL sees that as a literal character and returns no records.

So, I am wondering if there is a way to do a wildcard search using the code shown above or is there a different way to do this that will display the data in a similar format.

Thanks!

Answer 1

If your subquery (Temp) contains all the data you need you should be able to just use a matrix control in your report rather than a table.

The dataset query can just be the sub-query, no need for pivoting etc...

Add a row group grouped by netbios_name0 and model0, then add a column group grouped on displayname0 and set the value expression of the matrix "cell" to =MAX(version0)