Sql-server – select one row based on a string

querysql server

I have a table like this:

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;

and I'm getting for computer_id something like this:

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

Best Answer

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.

Related Solutions

Sql-server – Select multiple rows from one row based on column values

I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.

(Borrowing JNK's table variable)

SELECT id,
       Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

How about using with recursive

test view:

create view v as 
select *
from ( values ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) as baz ("name", "int");

query:

with recursive t("name", "int") as ( select "name", "int", 1 as span from v
                                     union all
                                     select "name", v."int", t.span+1 as span
                                     from v join t using ("name")
                                     where v."int"=t."int"+1 )
select "name", "start", "start"+span-1 as "end", span
from( select "name", ("int"-span+1) as "start", max(span) as span
      from ( select "name", "int", max(span) as span 
             from t
             group by "name", "int" ) z
      group by "name", ("int"-span+1) ) z;

result:

 name | start | end | span
------+-------+-----+------
 foo  |     2 |   4 |    3
 foo  |    13 |  13 |    1
 bar  |     1 |   3 |    3
 foo  |    10 |  11 |    2
(4 rows)

I'd be interested to know how that performs on your billion row table.

Best Answer

Related Solutions

Sql-server – Select multiple rows from one row based on column values

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

Related Question