Where clause in pivot-structured table

Question

I have a table in DB2 (oracle syntax) looking like this, let's call it SAMPLE

ID    TAG_NAME    VALUE
13    10          77
13    11          80
14    10          92
14    11          83

What I want to do is select all rows where TAG_NAME 10 has VALUE 77 and TAG_NAME 11 has VALUE 80. How can I do this? If I do like below it will try to create a situation where the TAG_NAME has to be both 10 and 11, while the VALUE has to be both 77 and 80. I don't want to select these specific tag names, rather when the tag name is x then it should check that the value is y etc.

SELECT 
    * 
FROM 
    SAMPLE
WHERE
    (TAG_NAME=10 AND VALUE=77)
    AND (TAG_NAME=11 AND VALUE=80)

The result I want to achieve is this:

ID    TAG_NAME    VALUE
13    10          77
13    11          80

Possible duplicate of [SQL: how to select a single id ("row") that meets multiple criteria from a single column](https://stackoverflow.com/questions/7179260/sql-how-to-select-a-single-id-row-that-meets-multiple-criteria-from-a-singl) — Clockwork-Muse, Aug 01 '18 at 16:30

score 1 · Accepted Answer · answered Aug 01 '18 at 15:58

1

You can use group by and having:

SELECT s.id
FROM SAMPLE s
WHERE (TAG_NAME = 10 AND VALUE = 77) OR
      (TAG_NAME = 11 AND VALUE = 80)
GROUP BY s.id
HAVING COUNT(DISTINCT TAG_NAME) = 2;

answered Aug 01 '18 at 15:58

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Aug 01 '18 at 18:28

1

I don't know DB2, but in Oracle you can do this:

select *
from   demo
where  (tag_name, value) in ((10,77), (11,80));

answered Aug 01 '18 at 18:28

William Robertson

15,273
4
38
44

score 0 · Answer 3 · answered Aug 01 '18 at 17:14

I think you solved your own question when you said:

If I do like below it will try to create a situation where the TAG_NAME has to be both 10 and 11, while the VALUE has to be both 77 and 80

If you change that to OR it should work.

SELECT 
    * .
FROM 
    SAMPLE
WHERE
    (TAG_NAME=10 AND VALUE=77)
    OR (TAG_NAME=11 AND VALUE=80)
    OR (TAG_NAME=x AND VALUE=y)

Where clause in pivot-structured table

3 Answers3