Get Concat result from two tables in Ms Sql

Question

Hi, Please ref my tables,

create table #ICT_Txn_QueriesHandlerDump
(
    [RowID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] INT,
	[QueryRaisedBy] [nvarchar](50) NULL,
	[QueryCategory] [nvarchar](200) NULL,
	[QueryDetails] [nvarchar](max) NULL,
	[CreatedOn] Datetime null

	)
	Insert Into #ICT_Txn_QueriesHandlerDump ([MemberID],[QueryRaisedBy],[QueryCategory],[QueryDetails],[CreatedOn]) 
	Values									(1001,			  'Exide',		   'Bank Related','KYC Missing','2023/04/01')
	Insert Into #ICT_Txn_QueriesHandlerDump ([MemberID],[QueryRaisedBy],[QueryCategory],[QueryDetails],[CreatedOn]) 
	Values									(1001,			  'BELSTAR',	   'Documents related','Document Not Updated','2023/04/02')
	Insert Into #ICT_Txn_QueriesHandlerDump ([MemberID],[QueryRaisedBy],[QueryCategory],[QueryDetails],[CreatedOn]) 
	Values									(1002,			  'KOTAK',		   'Courier Dispatch','Not Received','2023/04/05')
	Insert Into #ICT_Txn_QueriesHandlerDump ([MemberID],[QueryRaisedBy],[QueryCategory],[QueryDetails],[CreatedOn]) 
	Values									(1001,			  'BELSTAR',		   'KYC Documents','Document Not Updated','2023/04/04')


	create table #ICT_Txn_BranchResponseAgainstHOQueries
(
    [RowID] [int] IDENTITY(1,1) NOT NULL,
	[ParentRowID] INT,
	[BranchResponseRemarks] [nvarchar](50) NULL,
	[CreatedOn] Datetime null

	)
	Insert Into #ICT_Txn_BranchResponseAgainstHOQueries ([ParentRowID],[BranchResponseRemarks],[CreatedOn]) 
	Values									(1,			  'Information Provided','2023/04/02')

	Insert Into #ICT_Txn_BranchResponseAgainstHOQueries ([ParentRowID],[BranchResponseRemarks],[CreatedOn]) 
	Values									(2,			  'Documents Updated','2023/04/04')

	Insert Into #ICT_Txn_BranchResponseAgainstHOQueries ([ParentRowID],[BranchResponseRemarks],[CreatedOn])
	Values									(4,			  'Dispatch one','2023/04/04')

	Insert Into #ICT_Txn_BranchResponseAgainstHOQueries ([ParentRowID],[BranchResponseRemarks],[CreatedOn])
	Values									(3,			  'KOTAK Courier dispatched','2023-04-23')

Hereby Join these 2 columns [#ICT_Txn_QueriesHandlerDump].[RowID] = [#ICT_Txn_BranchResponseAgainstHOQueries].[ParentRowID] I wanna result like this.

Answer 1

Check this query:

select x.MemberID,
    (stuff(
        (select concat(',', CHAR(13), CHAR(10), 'HO: ', cast(d.CreatedOn as date), ' | ', d.QueryRaisedBy, ' | ', d.QueryCategory, ' | ', ' - BR: ', cast(q.CreatedOn as date), ' - ', q.BranchResponseRemarks)
        from #ICT_Txn_QueriesHandlerDump d
        inner join #ICT_Txn_BranchResponseAgainstHOQueries q on q.ParentRowID = d.RowID
        where d.MemberID = x.MemberID
        order by d.CreatedOn, d.RowID
        for xml	path(''), type 
        ).value('.', 'nvarchar(max)'), 1, 3, '')) as QueryAndResponses
from (select distinct MemberID from #ICT_Txn_QueriesHandlerDump) x
order by MemberID

Use the “Result to Text” option to view the multiline results.

Answer 2

I prepare my script, but small difference is there ref this.

WITH HOQueries AS (
	SELECT 
		'HO: ' + CONVERT(VARCHAR(10), CreatedOn, 110) + ' | ' + QueryRaisedBy + ' | ' + QueryCategory + ' | ' + QueryDetails AS QueryAndResponse,
		MemberID,
		RowID
	FROM #ICT_Txn_QueriesHandlerDump
), BRQueries AS (
	SELECT 
		'BR: ' + CONVERT(VARCHAR(10), b.CreatedOn, 110) + ' - ' + BranchResponseRemarks AS QueryAndResponse,
		q.MemberID,
		q.RowID
	FROM #ICT_Txn_BranchResponseAgainstHOQueries b
	INNER JOIN #ICT_Txn_QueriesHandlerDump q ON q.RowID = b.ParentRowID
)
SELECT 
	h.MemberID,
	STUFF((SELECT ', ' + q.QueryAndResponse
		   FROM HOQueries q 
		   WHERE q.MemberID = h.MemberID
		   ORDER BY q.RowID
		   FOR XML PATH('')), 1, 2, '') + ' - ' + STUFF((SELECT ', ' + b.QueryAndResponse
														  FROM BRQueries b
														  WHERE b.MemberID = h.MemberID
														  ORDER BY b.RowID
														  FOR XML PATH('')), 1, 2, '') AS QueryAndResponses
FROM HOQueries h
GROUP BY h.MemberID

Answer 3

Hi @Aypn CNN

If your SQL Server support STRING_AGG (SQL Server 2017 (14.x) and later), you could also try this query:

SELECT MemberID
      ,STRING_AGG(CONCAT(CAST(B.CreatedOn AS DATE),' | ',QueryRaisedBy,' | ',QueryCategory,' | ',QueryDetails,CHAR(13)),',')
	   WITHIN GROUP(ORDER BY B.CreatedOn,B.RowID) AS [HO Queries]
	  ,STRING_AGG(CONCAT(CAST(A.CreatedOn AS DATE),' - ',BranchResponseRemarks,CHAR(13)),'')
	   WITHIN GROUP(ORDER BY B.CreatedOn,B.RowID) AS [Branch Response]
FROM #ICT_Txn_BranchResponseAgainstHOQueries A JOIN #ICT_Txn_QueriesHandlerDump B ON A.ParentRowID=B.RowID
GROUP BY MemberID

Note that the grid view in SQL Server Management Studio doesn't display newlines, unless you click "Results to Text" button in the toolbar.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.