SQL : Filter Column with row wise values

Question

T-SQL:
Below is my table on which i need to apply row wise filter on a col. my criteria is that the Val should be : 0 AND (1 OR 1.1) AND 2

The original table is

name   val
aaa    0
aaa    1
aaa    1.1
aaa    2
bbb    0
bbb    2
ccc    0
ccc    1
ccc    2

The expected result will be

name   val
aaa     0
aaa     1
aaa     1.1
aaa     2
ccc     0
ccc     1
ccc     2

Could anyone suggest a solution. I am trying the Where clause but i am not able to give the conditions within the where clause. Appreciate any help

Dan Guzman · Accepted Answer · 2018-08-20 01:50:37Z

1

The following will return all rows from the group when the conditions are satisfied:

DECLARE @test TABLE(
      name varchar(10) NOT NULL
    , value varchar(10) NOT NULL
);

INSERT INTO @test(name, value)
VALUES
     ('aaa', '0')
    ,('aaa', '1')
    ,('aaa', '1.1')
    ,('aaa', '2')
    ,('bbb', '0')
    ,('bbb', '2')
    ,('ccc', '0')
    ,('ccc', '1')
    ,('ccc', '2');

SELECT a.name, a.value
FROM @test a
CROSS APPLY(SELECT COUNT(*) FROM @test b WHERE b.name = a.name AND value IN('0', '2')) AS b(cnt)
CROSS APPLY(SELECT COUNT(*) FROM @test b WHERE b.name = a.name AND value IN('1', '1.1')) AS c(cnt)
WHERE b.cnt = 2 AND c.cnt >= 1;

edited Aug 20, 2018 at 1:50

answered Aug 20, 2018 at 1:18

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eric Over a year ago

Why are you making the values varchar?

Dan Guzman Over a year ago

@Eric, just guessing at the data type. The sample data shows left-justified strings and no decimals for integer values.

Eric Over a year ago

fair enough, just keep in mind the OP may not have the best grasp of data types. I'd hate to see numeric data represented as text in a production db :)

TomC Over a year ago

Why cross apply and not simple left joins? left join (select name, count(*) as cnt from @test where value IN('0', '2') group by name) c02 on c02.name=a.name

Squirrel · Accepted Answer · 2018-08-20 01:23:36Z

0

The 3 conditions are specified as a cte and join to your original table. The matching rows should be 3

; with 
-- your table
tbl as    
(
    select  name   = 'aaa',    val = 0    union all
    select  name   = 'aaa ',   val = 1    union all
    select  name   = 'aaa',    val = 1.1  union all
    select  name   = 'aaa',    val = 2    union all
    select  name   = 'bbb',    val = 0    union all
    select  name   = 'bbb',    val = 2    union all
    select  name   = 'ccc',    val = 0    union all
    select  name   = 'ccc',    val = 1    union all
    select  name   = 'ccc',    val = 2
),
--  your condition
val as
(
    select  val1 = 0, val2 = 0    union all
    select  val1 = 1, val2 = 1.1  union all
    select  val1 = 2, val2 = 2
),
cte_name as
(
    select  t.name
    from    tbl t
            inner join val v    on  t.val   = v.val1
                                or  t.val   = v.val2
    group by name
    having count(distinct v.val1) = 3   -- matching rows should be 3
) 
-- the query
select  t.*
from    tbl t
        inner join cte_name n   on  t.name  = n.name

answered Aug 20, 2018 at 1:23

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

5 Comments

Eric Over a year ago

Interesting solution, but given that the OP is having issues with a basic where clause, I think this is way too complicated. I doubt the OP even knows what a CTE is. Not trying to be rude, but I kept my answer stupid simple for a reason.

Squirrel Over a year ago

@Eric, you are right. This might be a bit over kill. But your solution actually does not produce the result that OP wants. Because you only specify 2 condition 0 or 2 and 1 or 1.1. What OP wanted is to fulfill all 3 conditions

Eric Over a year ago

that's not correct, an in clause includes all the values. So its 0 and 2, not 0 or 2.

Squirrel Over a year ago

so for in (0, 2) condition, which row can satisfied both 0 and 2 ? try your query with my sample data or Dan's and see for yourself

Eric Over a year ago

just re-read the OPs question and realized the drop out of B. I misinterpreted the question. So, you're correct that my solution was incorrect : )

TomC · Accepted Answer · 2018-08-20 02:11:24Z

0

One other version:

DECLARE @test TABLE(
      name varchar(10) NOT NULL
    , value decimal(5,1) NOT NULL
);

INSERT INTO @test(name, value)
VALUES
     ('aaa', 0)
    ,('aaa', 1)
    ,('aaa', 1.1)
    ,('aaa', 2)
    ,('bbb', 0)
    ,('bbb', 2)
    ,('ccc', 0)
    ,('ccc', 1)
    ,('ccc', 2);

select a.name, a.value 
from @test a
join (
    select name, 
        sum(case when value IN(0,2) then 1 else 0 end) as c02, 
        sum(case when value IN(1,1.1) then 1 else 0 end) as c1 
    from @test group by name
    ) g on g.name=a.name and c02=2 and c1>=1

answered Aug 20, 2018 at 2:11

TomC

2,8241 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQL : Filter Column with row wise values

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related