All we need is an easy explanation of the problem, so here it is.
and I want to have the output like this:
If I use case to this, I’m not getting a unique value for each pc. I used:
case when software like '%7zip%' then software else 'no' end;
I don’t know what function to use and if I can do this with ‘case’.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
It’s not entirely clear what you’re trying to accomplish but I think you basically want only the
Computer_ids of the rows that have
7Zip or the
Computer_ids of the rows that don’t have
7Zip in any other row for that same
Computer_id, and you want the value to be
no. You can accomplish this with a
UNION ALL clause and an outer self-join like so:
-- This gets you only the rows with 7Zip in the Software field SELECT Computer_id, Software FROM TableName WHERE Software like '%7zip%' UNION ALL -- This gets you only the rows of Computer IDs who don't have Software containing 7Zip in any other row SELECT T1.Computer_id, 'no' AS Software FROM TableName T1 LEFT JOIN TableName T2 ON T1.Computer_id = T2.Computer_id AND T2.Software like '%7zip%' WHERE T1.Software NOT LIKE '%7Zip%' AND T2.Computer_id IS NULL
If you have the same
Computer_id more than once in either case and you only want one row for it, you can add the
DISTINCT clause to your
SELECT clause in each query of the
UNION ALL above too.
Below is an example using a CTE with conditional aggregation.
WITH inventory_per_computer AS ( SELECT Computer_id, SUM(CASE soft WHEN '7Zip' THEN 1 ELSE 0 END) AS Installed FROM dbo.SoftwareInventory GROUP BY Computer_id ) SELECT Computer_id, CASE Installed WHEN 0 THEN 'no' ELSE '7Zip' END AS Software FROM inventory_per_computer ORDER BY Computer_id;
You can also use a reference table and use it to do a left join like so:
create table demo (computer_id int, soft varchar(20)); insert into demo values (1,'Adobe'),(1,'Office'),(2,'Adobe'),(2,'7Zip'),(3,'7Zip'),(1,'7Zip') ,(4,'7Zip'),(5,'office'),(6,'Adobe'); select * from demo; with ref as (select distinct computer_id from demo) select r.computer_id, ISNULL(d.soft, 'no') from ref r left join demo d on r.computer_id = d.computer_id and d.soft = '7Zip' drop table demo;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂