CODEWITHSUNDEEP
sqlite
Vanilla Face
Vanilla Face

Reputation: 916

Sqlite how to select case for multiple values in a column

So, I have this table:

id|otherid|key|value
--------------------
1  1       ak  av
2  1       bk  bv
3  2       ak  av
3  2       ak  av2

The things to note is that other ids are repeating and they can have same keys with values multiple times. The thing I want to retrieve would be the value for the key, or, if there are multiple values for same key some string. So, I'd like to receive for otherids

otherid|key|value
-----------------
1       ak  av
1       bk  bv
2       ak  SEQUENCE

Where 'SEQUENCE' string allows me to know that there are multiple values for the single key for otherid. What query would accomplish this?

Upvotes: 0

Views: 962

Answers (2)

CL.
CL.

Reputation: 180060

To get one output row for multiple input rows, use grouping.

The count of rows in the group is available with COUNT(*); you can handle the cases with a CASE expression:

SELECT otherid,
       key,
       CASE COUNT(*)
       WHEN 1 THEN MIN(value)
       ELSE        'SEQUENCE'
       END AS value
FROM MyTable
GROUP BY otherid,
         key;

Upvotes: 1

Vanilla Face
Vanilla Face

Reputation: 916

SELECT DISTINCT
otherid,key,
(SELECT
    CASE
      WHEN COUNT(value)=1 THEN value
      WHEN COUNT(value)=0 THEN '*nil*'
      ELSE '*sequence*'
    END)
FROM datasingle
WHERE otherid=myid GROUP BY key;

Upvotes: 0

Related Questions