Combine rows from self-referenced table

Question

For an Excel export I have to add two columns to each item with all their "has-reference-to" and "is-referenced-from" items.

Here is my SQL Fiddle with my tables and my results.

My tables

CREATE TABLE Item (ItemId INT, Title NVARCHAR(100))
INSERT INTO Item VALUES(1, 'Title 1')
INSERT INTO Item VALUES(2, 'Title 2')
INSERT INTO Item VALUES(3, 'Title 3')
INSERT INTO Item VALUES(4, 'Title 4')
INSERT INTO Item VALUES(5, 'Title 5')

CREATE TABLE ItemReference (ReferenceId INT, FromItemId INT, ToItemId INT)
INSERT INTO ItemReference VALUES(1, 1, 2)
INSERT INTO ItemReference VALUES(2, 1, 3)
INSERT INTO ItemReference VALUES(3, 4, 5)
INSERT INTO ItemReference VALUES(4, 5, 1)

Both FromItemId and ToItemId contain an ItemId from table Item.

Expected output

+---------+------------------+--------------------+
| Title   | Has reference to | Is referenced from |
+---------+------------------+--------------------+
| Title 1 | Title 2, Title 3 | Title 5            |
+---------+------------------+--------------------+
| Title 2 |                  | Title 1            |
+---------+------------------+--------------------+
| Title 3 |                  | Title 1            |
+---------+------------------+--------------------+
| Title 4 | Title 5          |                    |
+---------+------------------+--------------------+
| Title 5 | Title 1          | Title 4            |
+---------+------------------+--------------------+

My query

SELECT item.Title,
    ISNULL([Has reference to], '') [Has reference to],
    ISNULL([Is referenced from], '') [Is referenced from]
FROM Item item
OUTER APPLY
(
    SELECT i.Title + ', '
    FROM ItemReference ref
    INNER JOIN Item i ON ref.FromItemId = i.ItemId
    WHERE ref.FromItemId = item.ItemId
    ORDER BY ref.ToItemId DESC
    FOR XML PATH('')
) OURT ([Has reference to])
OUTER APPLY
(
    SELECT i.Title + ', '
    FROM ItemReference ref
    INNER JOIN Item i ON ref.ToItemId = i.ItemId
    WHERE ref.ToItemId = item.ItemId
    ORDER BY ref.FromItemId DESC
    FOR XML PATH('')
) OURF ([Is referenced from])

Current output

+---------+------------------+--------------------+
| Title   | Has reference to | Is referenced from |
+---------+------------------+--------------------+
| Title 1 | Title 1, Title 1 | Title 1            |
+---------+------------------+--------------------+
| Title 2 |                  | Title 2            |
+---------+------------------+--------------------+
| Title 3 |                  | Title 3            |
+---------+------------------+--------------------+
| Title 4 | Title 4          |                    |
+---------+------------------+--------------------+
| Title 5 | Title 5          | Title 5            |
+---------+------------------+--------------------+

As you can see, the number of item references matches my expected results but I can't get the correct title of the references. Could anyone help me get the correct titles?

Answer 1

Sorry for my ignorance that ref doesn't have item column, without data sample I couldn't see the tree I think :)

SELECT item.Title,
    ISNULL([Has reference to], '') [Has reference to],
    ISNULL([Is referenced from], '') [Is referenced from]
FROM @Item item
OUTER APPLY
(
    SELECT i.Title + ', '
    FROM @ItemReference ref
    INNER JOIN @Item i ON ref.ToItemId = i.ItemId
    WHERE ref.FromItemId = item.ItemId
    ORDER BY ref.ToItemId DESC
    FOR XML PATH('')
) OURT ([Has reference to])
OUTER APPLY
(
    SELECT i.Title + ', '
    FROM @ItemReference ref
    INNER JOIN @Item i ON ref.FromItemId = i.ItemId
    WHERE ref.ToItemId = item.ItemId
    ORDER BY ref.FromItemId DESC
    FOR XML PATH('')
) OURF ([Is referenced from]);

Links should be ref.To and ref.From (were ref.From and ref.To).

EDIT: BTW, because of order by it is Title 3, Title 2. I am not sure if it is what you want.