CODEWITHSUNDEEP
sqlsql-server
Nikhil
Nikhil

Reputation: 51

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.

Upvotes: 0

Views: 52

Answers (2)

StepUp
StepUp

Reputation: 38094

Try to use GROUP BY AND aggregation functions:

  select
  t.t1
, CASE 
      WHEN MIN(t.t1) = 1930190 THEN NULL      
      WHEN MIN(t.t1) = 1930191 OR MIN(t.t1) = 1930192  THEN 333
      ELSE MIN(t.M1)
  END M1 
from test t
group by t.t1

Or to have better performance try to use a subquery:

SELECT
  q.t1
, CASE 
      WHEN q.t1 = 1930190 THEN NULL      
      WHEN q.t1 = 1930191 OR q.t1 = 1930192  THEN 333
      ELSE q.M1
  END M1
FROM
(
  SELECT
    t.t1
  , MIN(t.M1) M1 
  FROM test t
  GROUP BY t.t1
)q

OUTPUT:

  t1         M1
1930187    184962
1930188    184962
1930189    333
1930190    NULL
1930191    333
1930192    333

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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];

Upvotes: 2

Related Questions