Hi svibuk,
The output which you have mentioned above cannot be generated through a single line of query. For this you will have to create the function. So you can create the below function in SQL like this
--SELECT [dbo].[GetLanguages] ('000004')
CREATE FUNCTION GetLanguages
(
@MemberId VARCHAR(10)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Languages AS VARCHAR(50)
DECLARE @LanguagesKnown AS VARCHAR(MAX)
DECLARE @Char AS VARCHAR(1)
DECLARE @Length AS INT
DECLARE @I AS INT
SELECT @Languages = Languages FROM Details WHERE MemberId = @MemberId
SET @Length = LEN(@Languages)
SET @LanguagesKnown = ''
SET @I = 0
WHILE(@I <= @Length)
BEGIN
SET @Char = SUBSTRING (@Languages,@I,1)
IF @Char <> ','
BEGIN
SELECT @LanguagesKnown = (@LanguagesKnown + [Language] + ', ') FROM M_Language WHERE LID = CONVERT(INT,@Char)
END
SET @I = @I + 1
END
SET @LanguagesKnown = SUBSTRING(@LanguagesKnown,0,LEN(@LanguagesKnown)-1)
RETURN @LanguagesKnown
END
GO
now you will need to call this function through code and it will give the Language Names for that particular MemberId