SQL excluding something from a list

[glenimp617]

I have a SQL query which returns all installed software across our network. Like this:

Adobe Lightroom
AMD Driver v2.2.1
Microsoft Office 2020
Nvidia CC

It's 3,330 lines and I want to start building up a list of things to exclude (like drivers, and stuff we don't want).

Rather than having a query like this

SELECT * FROM TABLE WHERE 'Title' NOT LIKE 'AMD Driver%' AND 'Title' NOT LIKE 'Nvidia%' AND.......AND......AND

Can I make the query a bit more elegant and reference perhaps a second table with everything we don't want?

Thanks!!

 

[glenimp617]

is it that simple

 

[glenimp617]

Right, got it working great thanks

Next little problem to solve...... the table looks like the top part of this image, but I'd like it to look like the bottom part

 

[glenimp617]

That's effectively a pivot table you're asking for.
There are ways to create them but not with a single SQL statement. So it depends what platform you're on as to what you can use.

ok, thanks for the heads up. I might be able to do it in powershell.

 

[glenimp617]

You can use some unique element to it to return less results. Are you outputting this to excel ?

Yeah it's going into excel

 

[glenimp617]

Thanks everyone!!

I cheated in the end. Once I got the data right in excel, I gave it to our data science dept who knocked this up in about 5mins

 

[glenimp617]

If you had a whole team to ask in the first place lol.

We'll they wouldn't have done all the SQL queries from SCCM would they, they aren't the IT dept....and I enjoy learning new things. The first question helped me no end, and I can use that for other little projects I'm working on

 

[glenimp617]

Was a bit of sarc jesting bud, glad we could help. I like to learn this stuff too!

don't worry, I've added you to my list of oracles I'll no doubt need some more help soon