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.