Fetch values from Comma Seperated Columns in SQL SERVER

hi

try following code, it might work for you.

create table details
(
Name varchar(50),  
CategoryId varchar(50)
)

create table category
(
id int,
Name varchar(50),
)

Insert into category values(2,'Motor'),
(4  ,'Scooter'),
(6  ,'Car')
insert into details values('Gaurav', '4,6'),
('Amit','2,4'),
('Ajay','6,2')

GO
CREATE function dbo.getCatDetail(@category varchar(50))
returns varchar(100)
AS
BEGIN
 DECLARE @listStr VARCHAR(MAX)
 select @listStr = COALESCE(@listStr+',' ,'') + Name from dbo.fn_Split(@category,',')
 join category c on value = c.id
 RETURN @listStr
END
GO
CREATE FUNCTION [dbo].[fn_Split] (@ListValues nvarchar(max),@SplitBy nvarchar(5))   
RETURNS @ResultValue table ( Id int identity(1,1),Value nvarchar(100))  
AS   
BEGIN  
  While (Charindex(@SplitBy,@ListValues)>0) 
  Begin 
     Insert Into @ResultValue (value)
     Select  Value = ltrim(rtrim(Substring(@ListValues,1,Charindex(@SplitBy,@ListValues)-1))) 
     Set @ListValues = Substring(@ListValues,Charindex(@SplitBy,@ListValues)+len(@SplitBy),len(@ListValues)) 
   End
   Insert Into @ResultValue (Value)
   Select Value = ltrim(rtrim(@ListValues)) 
    Return 
END
GO

select Name,dbo.getCatDetail(CategoryId) as category from details

hope this will help you.