I have done this by using below code..
SELECT DISTINCT
SUBSTRING(Grp_kit_NO, 0, CHARINDEX('_', Grp_kit_NO)) GRP_KIT_NO, 0 rownum INTO #temp
FROM group_kit_detail
DECLARE @i int = 0
UPDATE #temp
SET @i = rownum = @i + 1
SELECT
GRP_KIT_NO,
CONVERT(varchar(max), '') comp,
0 RW INTO #T1
FROM GROUP_KIT_BOM
WHERE 1 = 2
SELECT
CONVERT(varchar(max), '') GRP_KIT_NO,
CONVERT(varchar(max), '') Match_GRP_KIT_NO INTO #Final
FROM GROUP_KIT_BOM
WHERE 1 = 2
DECLARE @RowCounter int = 1
WHILE @RowCounter <= (SELECT
COUNT(1)
FROM #temp)
BEGIN
DECLARE @MasterGroup varchar(50) = ''
SELECT
@MasterGroup = GRP_KIT_NO
FROM #temp
WHERE rownum = @RowCounter
SELECT
GRP_KIT_NO,
COMPONENT + COMP_TYPE + QTY comp,
0 Row1 INTO #TInput
FROM GROUP_KIT_BOM
WHERE SUBSTRING(Grp_kit_NO, 0, CHARINDEX('_', Grp_kit_NO)) = @MasterGroup
ORDER BY GRP_KIT_NO, comp
DECLARE @j int = 0
UPDATE #TInput
SET @j = Row1 = @j + 1
INSERT INTO #T1
SELECT
GRP_KIT_NO,
COMP = STUFF((SELECT
',' + COMP
FROM #TInput A
WHERE A.GRP_KIT_NO = B.GRP_KIT_NO
FOR xml PATH ('')), 1, 1, ''),
0
FROM #TInput B
GROUP BY B.GRP_KIT_NO
ORDER BY COMP
DROP TABLE #TInput
SET @RowCounter = @RowCounter + 1
END
DECLARE @k int = 0
UPDATE #T1
SET @k = RW = @k + 1
DECLARE @Counter int = 1-----------------matching comp
WHILE @Counter <= (SELECT
COUNT(1)
FROM #T1)
BEGIN
DECLARE @Group varchar(50) = ''
SELECT
@Group = GRP_KIT_NO
FROM #T1
WHERE RW = @Counter
INSERT INTO #Final
SELECT DISTINCT
A.GRP_KIT_NO,
B.GRP_KIT_NO
FROM #T1 A,
#T1 B
WHERE B.GRP_KIT_NO <> @Group
AND A.comp = B.comp
AND A.GRP_KIT_NO <> B.GRP_KIT_NO
ORDER BY A.GRP_KIT_NO
SET @Counter = @Counter + 1
END
SELECT
*
FROM #Final
DROP TABLE #Final
DROP TABLE #T1
DROP TABLE #TEMP