Help in Coalescing distinct values

Hi all,

I want to know how to coalesce distinct values as comma seperated into a variable which is used elsewhere. Here are my ddl and the query I tried.
My Expected result is
[Java],[MySQL],[.Net]

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)
SELECT DISTINCT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Lkp_SkillCategory.Skill+']'
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

The following is one of the method

Code Snippet

CREATE TABLE #Tbl_Request

(

ID INT,

SkillCategoryID INT

)

GO

CREATE TABLE #Lkp_SkillCategory

(

ID INT,

Skill varchar(50)

)

GO

INSERT INTO #Tbl_Request VALUES(1,0)

INSERT INTO #Tbl_Request VALUES(2,1)

INSERT INTO #Tbl_Request VALUES(3,2)

INSERT INTO #Tbl_Request VALUES(4,0)

INSERT INTO #Tbl_Request VALUES(5,2)

INSERT INTO #Tbl_Request VALUES(6,2)

INSERT INTO #Tbl_Request VALUES(7,1)

GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')

INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')

INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')

GO

--Add Temp table to hold distinct values

create table #temp ( Skill varchar(50) )

Insert into #temp(Skill)

SELECT distinct #Lkp_SkillCategory.Skill

FROM #Tbl_Request INNER JOIN

#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID

--Coalesce from Temp table

DECLARE @.listSkills nvarchar(max)

SELECT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Temp.Skill+']'

FROM #Temp

SELECT @.listSkills

--Drop the tables

DROP TABLE #Temp,#Lkp_SkillCategory,#Tbl_Request

|||

or you can try this one

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)

select @.listSkills = COALESCE(@.listSkills+',','') + '[' + Skill + '] '
FROM
(
SELECT distinct Skill
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
) tbl

SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

Thanks,

R@.j

|||

Code Snippet

Declare @.OutputString varchar(max)

select @.OutputString = IsNull(@.OutputString + ', ' + sValue , sValue)

from (

select distinct '[' + Skill + ']' as [sValue]

from [MyTable]

) as [SubQuery]

select @.OutputString

|||Thank you both rusag2 and Raj for the answer. Works perfectly fine.