Condition check, single/multiple value for multiple columns with comma separated column

user_4268241 0
Viewed 567

I have the following data:

create table mtest
(
 id1 int,
 id2 int,
 id3 int,
 id4 int,
 name varchar(20)
);


insert into mtest values(1,11,2,33,'Test1');
insert into mtest values(2,12,4,3,'Test2');
insert into mtest values(4,13,6,44,'Test3');
insert into mtest values(7,15,17,4,'Test4');
insert into mtest values(10,65,9,5,'Test5');
insert into mtest values(7,65,4,5,'Test6');
insert into mtest values(37,11,4,15,'Test7');

I want to find ID with the values : 7 and 4

Expected Output:

id_column   value   names
-------------------------------------
id3         4       Test2,Test6,Test7
id1         7       Test4,Test6
id1         4       Test3
id4         4       Test4

Try:

select distinct id_column,value,stuff((select ','+ name from mtest b where b.id1 = a.value or b.id2 = a.value or b.id3 = a.value or b.id4 =a.value for xml path('')), 1, 1,'') names
from
(
    select case when id1 in ('7','4') then 'id1'
                when id2 in ('7','4') then 'id2'
                when id3 in ('7','4') then 'id3'
                when id4 in ('7','4') then 'id4' else ''
            end as id_column,
            case when id1 in ('7','4') then id1
                when id2 in ('7','4') then id2
                when id3 in ('7','4') then id3
                when id4 in ('7','4') then id4 else ''
            end as value,
            name
    from mtest
) a
where a.id_column <> ''

But getting wrong result:

id_column   value   names
---------------------------------------------------
id1         4       Test2,Test3,Test4,Test6,Test7
id1         7       Test4,Test6
id3         4       Test2,Test3,Test4,Test6,Test7

And also I am worried about stuff clause for multiple or conditions because table is having more than 10 millions of records.

sql-server-2008-r2 sql-server sql
3 Answers

Firstly, you need to normalise your data (and by that I really mean fix your data, not do it on the fly). Then you aggregate your strings based on ID and IDCol (in my example).

WITH CTE AS(
    SELECT V.ID,
           V.IDCol,
           M.[Name]
    FROM dbo.mtest M
         CROSS APPLY (VALUES(id1,'id1'),(id2,'id2'),(id3,'id3'),(id4,'id4'))V(ID,IDCol))
SELECT C.IDCol,
       C.ID,
       STUFF((SELECT ',' + x.[Name]
              FROM CTE x
              WHERE C.IDCol = x.IDCol
                AND C.ID = x.ID
              FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,1,'') AS Names
FROM CTE C
WHERE C.ID IN (4,7)
GROUP BY C.IDCol,
         C.IDl

DB<>fiddle

Side note, SQL Server 2008 has 7 days left of (extended) support. you really need to look at upgrade paths ASAP.

user_20299830

You could do a UNPIVOT AND XML Path, the following query should do what you want:

;WITH CTE AS (
    SELECT Col, Val, Name
    FROM mtest a
    UNPIVOT (Val FOR Col IN ([id1],[id2],[id3],[id4])) unpiv
    WHERE Val IN (4,7) )

SELECT Col, Val, 
Name = STUFF(
             (SELECT ',' + Name 
              FROM CTE t1
              WHERE t1.Col = t2.Col AND t1.Val = t2.Val 
              FOR XML PATH (''))
             , 1, 1, '')
FROM CTE t2
GROUP BY Col, Val
user_81551440

You an achieve it with CTE:

;with cte as
(
  select * from (
    select case when id1 in ('7','4') then 'id1'
                when id2 in ('7','4') then 'id2'
                when id3 in ('7','4') then 'id3'
                when id4 in ('7','4') then 'id4'
            end as id_name,
            case when id1 in ('7','4') then id1
                when id2 in ('7','4') then id2
                when id3 in ('7','4') then id3
                when id4 in ('7','4') then id4
            end as id_column,
            name
    from @mtest
  ) a where id_column is not null
)

select id_column, id_name,
       (select name + ',' from cte where id_column = c.id_column and id_name = c.id_name for xml path(''))
from cte c
group by id_column, id_name
user_71325500
Related