Can I use string_split with enforcing combination of labels?

Question

So I have the following table:

Id    Name            Label
---------------------------------------
1     FirstTicket     bike|motorbike
2     SecondTicket    bike
3     ThirdTicket     e-bike|motorbike
4     FourthTicket    car|truck

I want to use string_split function to identify rows that have both bike and motorbike labels. So the desired output in my example will be just the first row:

Id    Name            Label
--------------------------------------
1     FirstTicket     bike|motorbike

Currently, I am using the following query but it is returning row 1,2 and 3. I only want the first. Is it possible?

SELECT Id, Name, Label FROM tickets
WHERE EXISTS (
        SELECT * FROM STRING_SPLIT(Label, '|')
        WHERE value IN ('bike', 'motorbike')
      )

Answer 1

You can use APPLY & do aggregation :

SELECT t.id, t.FirstTicket, t.Label
FROM tickets t CROSS APPLY
     STRING_SPLIT(t.Label, '|') t1
WHERE t1.value IN ('bike', 'motorbike')
GROUP BY t.id, t.FirstTicket, t.Label
HAVING COUNT(DISTINCT t1.value) = 2;

However, this breaks the normalization rules you should have separate table tickets.