question

ssc_guy avatar image
ssc_guy asked

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.

  • 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

sql-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered

This problem is very primitive, suggesting that OP might not be familiar with the basic features of T-SQL, such as CASE statements and aggregates. If this this the case then studying these topics might be a good idea.

One of the ways to solve this problem is by evaluating the required conditions separately. This way, it will be possible to pick correct value in case if there are rows for the same ID which satisfy both step-1 and step-2 conditions, as is the case with the sample data where ID = 1, i.e. first row satisfies step-1, and the second row - step-2 condition, in which case the value from the first row needs to be included in the final result. To evaluate whether the rows satisfies step-1 requirement, the case statement may be used:

case when x = 1 and y = 1 and z = 0 then Info else null end

Similarly, to evaluate whether the step-2 condition is met, the following case statement may be used:

case when x = 0 and y = 1 and z = 0 then Info else null end

When the conditions are evaluated separately, all that is left is to take the max of each and then pick the first if it is not null, else pick the second. If none of the conditions is met then NULL will be selected, automatically satisfying what is described in the question as step-3. Here is the complete script which will return desired results:

select
    ID, isnull(
        max(case when x = 1 and y = 1 and z = 0 then Info else null end),
        max(case when x = 0 and y = 1 and z = 0 then Info else null end)
    ) Info
    from [table-A]
    group by ID;

Based on the sample data in question, the query above returns the following:

ID          info
----------- ----------
1           dog
2           yellow
3           mon
4           NULL

Hope this helps.

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.