Skip to content
Advertisement

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:

enter image description here

Required Output:

enter image description here

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.

Advertisement

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