November 5, 2019 at 2:18 pm
Hello All
I have an issue regarding getting column value with flag=Y in all rows- as latest address.
Here is sample code - I was trying to do it without using a CTE/temp table
Is that possible in simple SQL query without using a cte?
Currently the output gives Latest Address in only row where Flag=Y but i want that Address in All rows.
Please advice.
drop table ##temp
Create table ##temp
(
Id int,
Name1 varchar(10),
Address1 varchar(20),
NewAddressFlag varchar(1),
CurrentAddress varchar(20)
)
Insert into ##temp
SELECT 111, 'JOHN', 'Red Street' , 'Y' , NULL UNION
SELECT 111, 'JOHN', 'Yellow Street' , 'N' , NULL UNION
SELECT 111, 'JOHN', 'Green Street' , 'N' , NULL UNION
SELECT 111, 'JOHN', 'Blue Street' , 'N' , NULL UNION
SELECT 222, 'MARK', 'Sun Street' , 'Y' , NULL UNION
SELECT 222, 'MARK', 'Beach Street' , 'N' , NULL UNION
SELECT 222, 'MARK', 'State Street' , 'N' , NULL UNION
SELECT 222, 'MARK', 'Main Street' , 'N' , NULL
------------------------------------
Select * from ##temp
--------Query to Derive Current address column----------------------------
Select * ,CASE WHEN NewAddressFlag = 'Y' THEN Address1 END as CurrentAddressDerived
from ##temp
Thanks [/font]
November 5, 2019 at 3:22 pm
select y.id,y.name1,x.address1,y.newaddressflag,y.current address from ##temp y inner join (select distinct id,address1 from #temp where newaddressflag='Y') x on x.id=y.id
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply