Want to compare 4 different columns with the result of CTE

Question

I have created a CTE (common table Expression) as follows:

DECLARE @N VARCHAR(100)

WITH CAT_NAM AS (    
    SELECT ID, NAME
    FROM TABLE1    
    WHERE YEAR(DATE) = YEAR(GETDATE())    
)    
SELECT @N = STUFF((
    SELECT ','''+ NAME+''''    
    FROM CAT_NAM    
    WHERE ID IN (20,23,25,30,37)   
    FOR XML PATH ('')    
),1,1,'')

The result of above CTE is 'A','B','C','D','F'

Now I need to check 4 different columns CAT_NAM_1,CAT_NAM_2,CAT_NAM_3,CAT_NAM_4 in the result of CTE and form it as one column like follow:

Select 
case when CAT_NAM_1 in (@N) then CAT_NAM_1
     when CAT_NAM_2 in (@N) then CAT_NAM_2
     when CAT_NAM_3 in (@N) then CAT_NAM_3
     when CAT_NAM_4 in (@N) then CAT_NAM_4
end as CAT
from table2

When I'm trying to do the above getting error please help me to do. If my approach is wrong help me with right one.

Do not combine separate values into one CSV, join as-is, then group if needed. — Akina, Aug 27 '21 at 09:19
`in (@N)` isn't doing what you think it does. It would be equivalent to `= @n`. — Thom A, Aug 27 '21 at 09:19
No 2 columns will have matching values for one cat_ID. I will select cat_ID also in the same query. — AshDil, Aug 27 '21 at 11:23

score 0 · Accepted Answer · answered Aug 27 '21 at 12:40

I am not exactly sure what you are trying to do, but if I understand the following script shows one possible technique. I have created some table variables to mimic the data you presented and then wrote a SELECT statement to do what I think you asked (but I am not sure).

DECLARE @TABLE1 AS TABLE (
    ID INT NOT NULL, 
    [NAME] VARCHAR(10) NOT NULL, 
    [DATE] DATE NOT NULL
);

INSERT INTO @TABLE1(ID,[NAME],[DATE])
VALUES (20, 'A', '2021-01-01'), (23, 'B', '2021-02-01'),
(25, 'C', '2021-03-01'),(30, 'D', '2021-04-01'),
(37, 'E', '2021-05-01'),(40, 'F', '2021-06-01');

DECLARE @TABLE2 AS TABLE (
    ID INT NOT NULL, 
    CAT_NAM_1 VARCHAR(10) NULL, 
    CAT_NAM_2 VARCHAR(10) NULL, 
    CAT_NAM_3 VARCHAR(10) NULL, 
    CAT_NAM_4 VARCHAR(10) NULL
);

INSERT INTO @TABLE2(ID,CAT_NAM_1,CAT_NAM_2,CAT_NAM_3,CAT_NAM_4)
VALUES (1,'A',NULL,NULL,NULL),(2,NULL,'B',NULL,NULL);

;WITH CAT_NAM AS (
    SELECT ID, [NAME]
    FROM @TABLE1 
    WHERE YEAR([DATE]) = YEAR(GETDATE()) 
    AND ID IN (20,23,25,30,37,40)
)
SELECT CASE 
    WHEN EXISTS(SELECT 1 FROM CAT_NAM WHERE CAT_NAM.[NAME] = CAT_NAM_1) THEN CAT_NAM_1 
    WHEN EXISTS(SELECT 1 FROM CAT_NAM WHERE CAT_NAM.[NAME] = CAT_NAM_2) THEN CAT_NAM_2
    WHEN EXISTS(SELECT 1 FROM CAT_NAM WHERE CAT_NAM.[NAME] = CAT_NAM_3) THEN CAT_NAM_3
    WHEN EXISTS(SELECT 1 FROM CAT_NAM WHERE CAT_NAM.[NAME] = CAT_NAM_4) THEN CAT_NAM_4 
    ELSE '?' -- not sure what you want if there is no match
END AS CAT
FROM @TABLE2;

yes, the above mentioned is what I wanted. Please let me know, if the table1 and table2 are in two different servers, then how to get the above solution. Thanks. — AshDil, Aug 27 '21 at 16:00
@AshDil if the data is on two different servers, then you could consider a linked server from one server to another and then use it that way as shown in the answer to https://stackoverflow.com/questions/4091960/sql-server-linked-server-example-query/4091984 . You can research linked servers to see if that would work for you. — Bjorg P, Aug 27 '21 at 19:24

score 0 · Answer 2 · answered Aug 27 '21 at 13:37

0

You can do a bit of set-based logic for this

SELECT
    ct.NAME
FROM table2 t2
CROSS APPLY (
    SELECT v.NAME
      FROM (VALUES
        (t2.CAT_NAM_1),
        (t2.CAT_NAM_2),
        (t2.CAT_NAM_3),
        (t2.CAT_NAM_4)
      ) v(NAME)
    INTERSECT
    SELECT ct.NAM
      FROM CAT_NAM ct
      WHERE ct.ID IN (20,23,25,30,37)
) ct;

answered Aug 27 '21 at 13:37

Charlieface

52,284
6
19
43

Hi I will try out your approach. Thanks. – AshDil Aug 27 '21 at 16:00

Want to compare 4 different columns with the result of CTE

2 Answers2