Getting a comma-delimited list of PK's for duplicates of a record in SQL Server 2005?

Question

This is an off-shoot of a previous question I had: http://stackoverflow.com/questions/1544314/a-little-fuzzy-on-getting-distinct-on-one-column

This query makes a little more sense, given the data:

SELECT     Receipts.ReceiptID, FolderLink.ReceiptFolderID
FROM         dbo.tbl_ReceiptFolderLnk AS FolderLink INNER JOIN
             dbo.tbl_Receipt AS Receipts ON FolderLink.ReceiptID = Receipts.ReceiptID

With results:

ReceiptID            ReceiptFolderID       NewColumn (duplicate folder ID list)
-------------------- ---------------      ----------
1                    3
2                    3
3                    7
4     <--->          4                       8,9
5                    4
6                    1
3                    8
4     <--->          8                       4,9
4     <--->          9                       4,8

That answer provided me to view distinct(ReceiptID)'s... great. Now, for those ID's, 3 and 4, they exist in multiple ReceiptFolderID's.

Given this NON-unique list of ReceiptID's, I'd like an additional column, of comma-delimited ReceiptFolderLinkID's where the ReceiptID also exists.

So for ReceiptID=4, the new column, say, DuplicateFoldersList, should read, "8,9", etc, and similar with ID=3, or any other duplicates.

So basically, I'd like another column to indicate the ReceiptFolderID's additional occurrences of ReceiptID in other folders.

Thanks!

Answer 1

In Mysql there is group_concat aggregate function, but in T-SQL and oracle you need to use another approach... This site lists multiple approaches for T-SQL, but none are very simple and easy (as mysql is)

Answer 2

You can create a function that, given a ReceiptID and the "current" ReceiptFolderID for that row, returns the other ReceiptFolderIDs as a concatenated, comma-delimited list. Example:

CREATE FUNCTION [dbo].[GetOtherReceiptFolderIDs](@receiptID int, @receiptFolderID int)
RETURNS varchar(MAX) AS
BEGIN
    DECLARE @returnValue varchar(MAX)

    SELECT   @returnValue = COALESCE(@returnValue + ', ', '') + COALESCE(CONVERT(varchar(MAX), ReceiptFolderID), '')
    FROM     tbl_ReceiptFolderLink AS FolderLink
    WHERE    FolderLink.ReceiptID = @receiptID
    AND      FolderLink.ReceiptFolderID <> @receiptFolderID

    RETURN @returnValue
END

Then, you can run a query that uses this function to obtain your new column:

SELECT      Receipts.ReceiptID, ReceiptFolderID, dbo.GetOtherReceiptFolderIDs(Receipts.ReceiptID, ReceiptFolderID) AS NewColumn
FROM        tbl_Receipt AS Receipts
INNER JOIN  tbl_ReceiptFolderLink AS FolderLinks
ON          Receipts.ReceiptID = FolderLinks.ReceiptID

I tested this and it produces the following results (if I got your schema correctly):

ReceiptID    ReceiptFolderID    NewColumn
6            1                  NULL
1            3                  NULL
2            3                  NULL
4            4                  8, 9
5            4                  NULL
3            7                  8
3            8                  7
4            8                  4, 9
4            9                  4, 8

Answer 3

Try:

SELECT r.receiptid, 
       t.receiptfolderid,
      (SELECT rfl.receiptfolderlinkid + ‘,’
         FROM dbo.tbl_ReceiptFolderLnk rfl
        WHERE rfl.receiptid = t.receiptid
     ORDER BY rfl.receiptfolderlinkid
      FOR XML PATH(”)) AS DuplicateFoldersList
  FROM dbo.tbl_ReceiptFolderLnk AS t
  JOIN dbo.tbl_Receipt AS r ON r.receiptid = t.receiptid