SQL Query for getting the output as shown in the example shared

For this sample data:

create table test (T1 varchar(20), M1 varchar(20))
insert into test (T1, M1) values('1930188', '184962')
insert into test (T1, M1) values('1930188', '185007')
insert into test (T1, M1) values('1930188', '61130783')
insert into test (T1, M1) values('1930188', '61110267')
insert into test (T1, M1) values('1930189', '333')
insert into test (T1, M1) values('1930189', '61120454')
insert into test (T1, M1) values('1930189', '61130779')
insert into test (T1, M1) values('1930189', '61110750')
insert into test (T1, M1) values('1930190', '195928')
insert into test (T1, M1) values('1930190', '61120454')
insert into test (T1, M1) values('1930190', '184541')
insert into test (T1, M1) values('1930190', '61130988')
insert into test (T1, M1) values('1930191', '155882')
insert into test (T1, M1) values('1930191', '333')
insert into test (T1, M1) values('1930191', '61130995')
insert into test (T1, M1) values('1930191', '61130994')
insert into test (T1, M1) values('1930191', '151261')
insert into test (T1, M1) values('1930191', '61391022')
insert into test (T1, M1) values('1930192', '333')
insert into test (T1, M1) values('1930192', '61202082')
insert into test (T1, M1) values('1930192', '61201725')
insert into test (T1, M1) values('1930192', '61131003')
insert into test (T1, M1) values('1930192', '61131002')
insert into test (T1, M1) values('1930191', '61130997')
insert into test (T1, M1) values('1930191', '61130977')
insert into test (T1, M1) values('1930187', '184962')
insert into test (T1, M1) values('1930187', '185007')
insert into test (T1, M1) values('1930187', '61110267')

And the query:

SELECT DISTINCT [T1], CASE WHEN [M1] IN ('184962', '333') THEN M1 END M1
FROM [test] order by T1

OUTPUT With above Query:

Required Output:

Notice the Null is required if no match, as shown in 4th row. The output must be display for all values in T1 and only values 184962 or 333 in M1 whichever is present and Null in M1 if value not present.

Answer

You can use APPLY :

SELECT [T1], MAX(m11)
FROM [test] t CROSS APPLY 
     ( VALUES (CASE WHEN [M1] IN ('184962','333')  
                    THEN M1 
               END)
     )  tt(m11)
GROUP BY [T1];

Advertisement

Answer