If you want to use CTE, please see:
DECLARE @tmp TABLE (atype VARCHAR(5), Country VARCHAR(30), aYear INT, afilename VARCHAR(30), FileID INT)
INSERT INTO @tmp (atype, Country, aYear, afilename, FileID)
VALUES('A', 'Singapore', 2010, 'A.xls', 20),
('A', 'Singapore', 2010, 'B.xls', 21),
('A', 'Singapore', 2010, 'C.Xls', 22),
('A', 'Malaysia', 2008, 'A.xls', 55),
('A', 'Malaysia', 2008, 'B.xls', 53),
('B', 'Malaysia', 2008, 'Cat.xls', 88),
('B', 'Malaysia', 2008, 'doll.xls', 78)
;WITH CTE AS
(
SELECT atype, Country, aYear, afilename, CONVERT(VARCHAR(MAX),afilename) AS fnames, FileID, CONVERT(VARCHAR(MAX),FileID) AS fIds, 1 AS LoopNo
FROM @tmp
UNION ALL
SELECT C.atype, C.Country, C.aYear, C.afilename, C.fnames + ',' + T.afilename AS fnames, C.FileID, C.fids + ',' + T.FileId AS fIds, LoopNo + 1 AS LoopNo
FROM CTE AS C INNER JOIN (
SELECT atype, Country, aYear, CONVERT(VARCHAR(MAX),afilename) AS afilename, CONVERT(VARCHAR(MAX),FileID) AS FileID
FROM @tmp
) AS T ON C.aType = T.aType AND C.Country = T.Country AND C.aYear = T.aYear AND C.FileID < T.FileID
WHERE CHARINDEX(CONVERT(NVARCHAR(10),T.FileID), C.fIds)=0
)
SELECT C.atype, C.Country, C.aYear, fnames, fIds
FROM CTE AS C
ORDER BY C.atype, C.Country, C.aYear
Above query produces:
A Malaysia 2008 A.xls 55
A Malaysia 2008 B.xls 53
A Malaysia 2008 B.xls,A.xls 53,55
A Singapore 2010 B.xls,C.Xls 21,22
A Singapore 2010 A.xls,B.xls 20,21
A Singapore 2010 A.xls,C.Xls 20,22
A Singapore 2010 A.xls,C.Xls,B.xls 20,22,21
A Singapore 2010 A.xls,B.xls,C.Xls 20,21,22
A Singapore 2010 A.xls 20
A Singapore 2010 B.xls 21
A Singapore 2010 C.Xls 22
B Malaysia 2008 Cat.xls 88
B Malaysia 2008 doll.xls 78
B Malaysia 2008 doll.xls,Cat.xls 78,88
There is no guarantee that CTE will be faster than in-build SQL functions. Change the code to your needs.
Note: Above sample code shows how CTE work.
For further information about CTE, please see:
WITH common_table_expression (Transact-SQL)[
^]
CTEs (Common Table Expressions)[
^]
CTE In SQL Server[
^]