How to modify and apply concatination on the following result
This is the first table:
DECLARE @item_tbl TABLE ( ItemId INT,Title NVARCHAR(20))
INSERT INTO @item_tbl VALUES (1,'Title 1'),(2,'Title 2')
,(3,'Title 3'),(4,'Title 4'),(5,'Title 5')
This is the second table:
DECLARE @item_ref TABLE (ReferenceId INT, FromItemId INT,
ToItemId INT)
INSERT INTO @item_ref VALUES(1,1,2),(2,1,3),(3,4,5),(4,5,1)
There is some interelation between table 1 & table 2
SELECT AA.Title, ISNULL(BB.Hasreferenceto,'') Hasreferenceto,
ISNULL(CC.Isreferencedfrom,'') Isreferencedfrom FROM
@item_tbl AA LEFT JOIN ( SELECT A.FromItemId, B.Title Hasreferenceto
FROM @item_ref A LEFT JOIN @item_tbl B ON A.ToItemId =B.ItemId
) BB ON AA.ItemId = BB.FromItemId
LEFT JOIN ( SELECT C.ToItemId, D.Title Isreferencedfrom
FROM @item_ref C LEFT JOIN @item_tbl D ON C.FromItemId = D.ItemId
) CC ON AA.ItemId = CC.ToItemId
This is giving me:
+---------+------------------+--------------------+
| Title | Hasreferenceto | Isreferencedfrom |
+---------+------------------+--------------------+
| Title 1 | Title 2 | Title 5 |
+---------+------------------+--------------------+
| Title 1 | Title 3 | Title 5 |
+---------+------------------+--------------------+
| Title 2 | | Title 1 |
+---------+------------------+--------------------+
| Title 3 | | Title 1 |
+---------+------------------+--------------------+
| Title 4 | Title 5 | |
+---------+------------------+--------------------+
| Title 5 | Title 1 | Title 4 |
+---------+------------------+--------------------+
What i want is:
+---------+------------------+--------------------+
| Title | Hasreferenceto | Isreferencedfrom |
+---------+------------------+--------------------+
| 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 |
+---------+------------------+--------------------+
Can someone help in this please?
