0
I am needing to make a query in sql server so that it identifies to me when combinations 1,2 and 3 or 2 and 3 of the "desc" column are present for repeated values of the "Cod" column the result should be returned as 1 for found and 0 not found. Does anyone know how to do this consultation? Thank you.
1
You can use a query to analyze the combinations using STUFF, see this link: https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation
And from this query, analyze the combined values to resume the id with CASE.
The query will look like this:
WITH combinado (cod, descr)
AS (SELECT cod,
-- combina os valores 1,2 e 3, agrupado por cod
Stuff((SELECT ', ' + Cast(descr AS CHAR(1))
FROM teste AS t2
WHERE t2.cod = t.cod
ORDER BY descr
FOR xml path(N'')), 1, 2, N'') AS descr
FROM teste t)
SELECT cod,
descr,
CASE descr
WHEN '1, 2, 3' THEN 1
WHEN '2, 3' THEN 1
ELSE 0
END AS ID
FROM combinado
See an example working here fiddle: http://sqlfiddle.com/#! 18/da106/15
Thank you very much colleague helped a lot. abs
Browser other questions tagged sql-server
You are not signed in. Login or sign up in order to post.
It was not very clear, you want to return a search where the desc is equal to id?
– Marceloawq
No, the "id" is the answer just look at the columns "Cod" and "desc" when the letters are repeated in "Cod" I want to check if within the repetitions there are the combinations mentioned. In example B repeats and has combination 1,2 and 3 in column "desc", C has combination 2 and 3 and finally H has combination 1,2 and 3. Thank you
– aguch
Trying to explain more clearly, I want every time you have a repetition of the letters in the "Cod" column to be checked in the "desc" column as if the numbers 1 and 2 and 3 no matter the order is contained within the repetitions this also applies to the combination 2 and 3 no matter the order. Thank you.
– aguch