select one row based on a string

All we need is an easy explanation of the problem, so here it is.

I have a table like this:
select one row based on a string

and I want to have the output like this:

select one row based on a string

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;

and I’m getting for computer_id something like this:
select one row based on a string

I don’t know what function to use and if I can do this with ‘case’.
Thank you!

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.

Method 1

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 Sofrware of 7Zip or the Computer_ids of the rows that don’t have Software of 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.

Method 2

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;

Method 3

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply