>Im trying a bit difficult select:
>
>select id, value
>from MyTable
>where id in (1, 1, 1, 2, 3, 3, 4)
>
>I want that for every value inside IN a row is output, so for example this would result something like:
>1 | 40
>1 | 40
>1 | 40
>2 | 21
>3 | 23
>3 | 23
>4 | 87
>
>But now I don't take duplicates, result is
>1 | 40
>2 | 21
>3 | 23
>4 | 87
>
>Can someone help me to create that tricky select?

Well, I'd say your problem description is brilliant, but that you have a completely wrong idea about how SELECTs are supposed to work. The idea with a SELECT is to return the ROWS that match the CRITERIA, not to return CRITERIA and ROWS that match(suppose a query like: SELECT ... WHERE (AGE < 50 AND LANGUAGE = 'Spanish') OR (AGE < 30). You would only want one row returned for someone below 30 that already know Spanish).

You could of course tweak what you want, e.g. using a recursive CTE (I'm assuming your list will not exceed 255 characters, you can of course expand it to a few thousand, though there is a limit as to how deep recursion is allowed in Firebird):

WITH RECURSIVE tmp(id, remainingids)
as
(SELECT 1, '1, 1, 2, 3, 3, 4'
FROM rdb$DATABASE
UNION ALL
SELECT CAST(case
when position(', ', remainingids) = 0 then remainingids
else substring(remainingids from 1 for position(', ', remainingids)-1)
end
as integer),
case
when position(', ', remainingids) = 0 then ''
else trim(substring(remainingids from position(', ', remainingids)+1 for 255))
end
FROM tmp
where remainingids > '')

select mt.id, mt.value
from tmp t
join MyTable mt on t.id = mt.id

HTH,
Set