Query for multiple rows

Question

Hello,
I've a table data as shown below.

DECLARE @prod TABLE (id int, abr int, ubr int)
INSERT INTO @prod
VALUES
(1 , 1 , 0),
(1 , 0 , 1),
(2 , 0 , 1),
(2 , 1 , 0)
select * from @prod

For - ID, I've abr value in one row. UBR value in another row.
I want both values in one row.
I want results as shown below:

id abr ubr
1 1 1
2 1 1

Can you please help me with this.

Answer 1

Assuming the value you want is the one which is not equal to 0, then

Select id,
  Max(Case When abr <> 0 Then abr Else Null End) As abr,
  Max(Case When ubr <> 0 Then ubr Else Null End) As ubr
From @prod
Group By id
Order By id;

Tom

Answer 2

Hi @VaralaxmiPandikonda-4750，

Please refer to:

DECLARE @prod TABLE (id int, abr int, ubr int)  
INSERT INTO @prod  
VALUES  
(1 , 1 , 0),  
(1 , 0 , 1),  
(2 , 0 , 1),  
(2 , 1 , 0)   
;with cte  
as(select id,case when abr=0 then ubr else abr end abr,case when ubr=0 then abr else ubr end ubr,  
row_number() over(partition by id order by id)rn   
from @prod)  
  
select id,abr,ubr from cte   
where rn<2  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.