I have a table-A, which is having five columns namely ID, x, y, z and Info. Need to find out Info column calculation based on remaining columns in the table

Transact-SQL
1

please find the table-A structure below:
ID x y z Info

1 1 1 0 dog

1 0 1 0 cat

1 1 1 1 rat

1 0 1 1 mouse

1 0 0 0 none

2 1 1 1 green

2 0 1 0 yellow

2 0 0 0 red

3 0 1 1 sun

3 1 1 0 mon

4 0 1 1 kill

Required logic :

above table needs to be group by using ID column &

Step-1: if x=1, y=1 and z=0 then take info value, else go to step-2

Step-2: if x=0, y=1 and z=0 then take info value, else go to step-3

Step-3: Other than above steps, we need to put null as info value

and required output mentioned in below

ID Info

1 dog

2 yellow

3 mon

4 Null

2 
select top(1) with ties
       id
      ,case
          when (x=1
           and  y=1
           and  z=0
               )
            or (x=0
           and  y=1
           and  z=0
               )
          then info
          else null
       end as info
  from [table-a]
 order by row_number() over(partition by id
                                order by case
                                            when x=1
                                             and y=1
                                             and z=0
                                            then 0
                                            when x=0
                                             and y=1
                                             and z=0
                                            then 1
                                            else 2
                                         end
                           )
;
1 Like
3 
Select Top (1) With ties
	id,
	Choose(y&(y^z), info)
From #temp
Order By ROW_NUMBER() Over (Partition By Id Order By y Desc, z Asc,x Desc)
1 Like
4

Assuming that x can be only 0 or 1, then all you really need is:

case when y=1 and z=0 then info else null end as info

5

I thought that at first, but the value of info you need depends on the value of x so you need to order the rows carefully to get the correct results.