Doubt

Hello @magesh you can try this method

 

 

select * Into ##tmp from (select Tab.Product_id ,Tab.Product_name From
(
select * from
(
select *,ROW_NUMBER() over(Partition by Product_id Order By Product_Id) as Row_ from Product_master
)Tab
)Tab
where Tab.Row_=1
)Tab


select * Into ##tmp1 from (select Tab.Product_id ,Tab.Product_name From
(
select * from
(
select *,ROW_NUMBER() over(Partition by Product_id Order By Product_Id) as Row_ from Product_master
)Tab
)Tab
where Tab.Row_=2
)Tab


Select ##tmp.Product_ID , ##tmp.Product_Name + ' '+ (case when ##tmp1.Product_Name is not null then ##tmp1.Product_Name else ' ' end) as Name from ##tmp
left outer join
##tmp1
on
##tmp.Product_ID=##tmp1.Product_ID

Drop table ##tmp , ##tmp1

 

this method quit complex but it give you correct answer . it will try a small method if i found then i will post.....

If you have any doubt then please ask............