How to get value as string in SQL Server

Question

I have a table tbl_Marks:

Std_id  | sub_id  |  term_I   | term_2
--------+---------+-----------+-------
std_1   | 1       |   40      | 50
std_1   | 2       |   30      | 40
std_1   | 3       |   20      | 30
std_1   | 4       |   10      | 50

std_2   | 1       |   50      | 50
std_2   | 2       |   50      | 50
std_2   | 3       |   50      | 50
std_2   | 4       |   50      | 50

How can I get result like this:

Std_id  | sub_id  |  term_I   | term_2 | total  | status | PROMOTION_status
--------+---------+-----------+--------+--------+--------+------------------
std_1   | 1       |   40      | 50     | 90     | PASS   | REPEATER
std_1   | 2       |   30      | 40     | 70     | PASS   | REPEATER
std_1   | 3       |   20      | 20     | 40     | FAIL   | REPEATER
std_1   | 4       |   10      | 50     | 60     | PASS   | REPEATER

Note : if total value is less than 50 of any sub_id

std_2   | 1       |   50      | 50     | 100    | PASS   | PROMOTED
std_2   | 2       |   50      | 50     | 100    | PASS   | PROMOTED
std_2   | 3       |   50      | 50     | 100    | PASS   | PROMOTED
std_2   | 4       |   50      | 50     | 100    | PASS   | PROMOTED

Note: if total value is greater than 50 or equal of each sub_id

Please help!

Radim Bača · Accepted Answer · 2017-12-22 15:53:57Z

1

Use CASE.

 select t.*,
        t.term_I + t.term_2 as total,
        case when t.term_I + t.term_2 >= 50 then 'pass' else 'fail' end as status,
        case when t.std_id = 'std_2' then 'PRMOTED' else 'REAPEATER' end as promotion_status
 from tbl_marks t

edited Dec 22, 2017 at 15:53

answered Dec 22, 2017 at 15:24

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nityanand Vishwakarma Over a year ago

There are two type of std_id, one belong to repeater and another one belong to promoted... while executing query value returns repeater of both std_id

Radim Bača Over a year ago

@NityanandVishwakarma check it now

Nityanand Vishwakarma Over a year ago

Sorry for disturbing... In case if std_id is more than 50, then what should Id do? Please don't mind.. Because Std_Id will be more thatn 50....

Eralper · Accepted Answer · 2017-12-25 10:39:25Z

0

Following SQL Select statement can help Instead of SQL CTE expression you can also use subselect statements But the main trick in this query is using MIN aggregation function with Partition by clause This helps you to check if there is a lower than 50 mark for that student, if so it makes all records for that student as REPEATER status

;with cte as (
select  
*, 
term_1 + term_2 as total
from tbl_Marks
)
select
*,
case when (term_1 + term_2) >= 50 then 'PASS' else 'FAIL' end as status,
case when (
min(total) over (partition by Std_id)
) >= 50 then 'PROMOTED' else 'REPEATER' end as PROMOTION_status
from cte

I hope it helps,

answered Dec 25, 2017 at 10:39

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to get value as string in SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related