Query to select multiple columns based on CASE statement in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

Hi! I used below script, but I want check column if Price <> 0 and PriceNow = 0 then show Price else if Price <> 0 and PriceNow <> 0 then show PriceNow.

 

DECLARE @Person AS TABLE(Id INT,Name VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Asror')

DECLARE @Detail AS TABLE(Id INT, PersonId int,Price INT, PriceNow FLOAT,Code VARCHAR(2))
INSERT INTO @Detail VALUES(1,1,9, 4,'AA')
INSERT INTO @Detail VALUES(2,1,8, 0,'AA')
INSERT INTO @Detail VALUES(3,1,7, 3,'AA')
INSERT INTO @Detail VALUES(4,1,6, 0,'AA')
INSERT INTO @Detail VALUES(6,1,15, 0,'BB')
INSERT INTO @Detail VALUES(7,2,14, 7,'BB')
INSERT INTO @Detail VALUES(8,2,13, 0,'BB')
INSERT INTO @Detail VALUES(9,2,4, 0,'CC')
INSERT INTO @Detail VALUES(10,2,5, 0,'CC')

select p.Name, d.Price, d.Code from @Detail d
inner join @Person p on p.Id = d.PersonId

I want below result for example:

Name

Price

Code

Rustam

4

AA

Rustam

8

AA

Rustam

3

AA

Rustam

6

AA

Rustam

15

BB

Asror

7

BB

Asror

13

BB

Asror

4

CC

Asror

5

CC

You are viewing reply posted by: dharmendr one year ago.
Posted one year ago

Hi PRA,

Refer below sample query. Use Case statement.

SQL

DECLARE @Person AS TABLE(Id INT,Name VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Asror')
 
DECLARE @Detail AS TABLE(Id INT, PersonId int,Price INT, PriceNow FLOAT,Code VARCHAR(2))
INSERT INTO @Detail VALUES(1,1,9,4,'AA')
INSERT INTO @Detail VALUES(2,1,8,0,'AA')
INSERT INTO @Detail VALUES(3,1,7,3,'AA')
INSERT INTO @Detail VALUES(4,1,6,0,'AA')
INSERT INTO @Detail VALUES(6,1,15,0,'BB')
INSERT INTO @Detail VALUES(7,2,14,7,'BB')
INSERT INTO @Detail VALUES(8,2,13,0,'BB')
INSERT INTO @Detail VALUES(9,2,4,0,'CC')
INSERT INTO @Detail VALUES(10,2,5,0,'CC')
 
select p.Name, 
CASE WHEN d.Price <> 0 AND PriceNow = 0 THEN d.Price 
	 WHEN d.Price <> 0 AND PriceNow <> 0 THEN d.PriceNow 
END as 'Price',
d.Code
from @Detail d
inner join @Person p on p.Id = d.PersonId

Output

Name Price Code
Rustam 4 AA
Rustam 8 AA
Rustam 3 AA
Rustam 6 AA
Rustam 15 BB
Asror 7 BB
Asror 13 BB
Asror 4 CC
Asror 5 CC