MS SQL Row to Column

Question

Hi, please understand my requirement and provide ms sql 2016 script, I have two tables (Table structure with sample data and expected result details are attached)

(Don't use 'STRING_AGG' )

Table 1 : [ICT_Log_ScannedDocumentTransmission_Branch]

Table Name2: [dbo].[ICT_Log_ScannedDocumentTransmission_HO]

Table Structure: TABLE_SCRIPT.txt

Expected Result(Attached) Expected_Result_Final.txt

Answer 1

Maybe you need a query like this:

; with Q as
(
    select 
        b.ApplTrackingNo,
        concat( 'Branch: UploadOn: ', b.CreatedOn, ', Comment: ', b.FileTypeCategory, ', HO: VerifiedOn: ', h.CreatedOn, ', Status: ',
        case h.IsApproved when 2 then 'Rejected' when 1 then 'Approved' end, ', Remarks: ', b.Remarks ) as WorkLog,
        row_number() over (partition by b.ParentTblRowID order by b.CreatedOn) as n
    from #ICT_Log_ScannedDocumentTransmission_Branch b
    left join #ICT_Log_ScannedDocumentTransmission_HO h on h.ParentTblRowID = b.ParentTblRowID and h.CreatedOn > b.CreatedOn
    left join #ICT_Log_ScannedDocumentTransmission_HO h2 on h2.ParentTblRowID = h.ParentTblRowID and h2.CreatedOn > b.CreatedOn and h2.CreatedOn < h.CreatedOn
    where h2.ParentTblRowID is null
)
select Q.ApplTrackingNo,
    concat( stuff( (select ' | ' + WorkLog from Q t where t.ApplTrackingNo = Q.ApplTrackingNo order by n for xml path ('') ), 1, 3, ''),
        ' | BrunchUploadCycle: ', count(*)) as WorkLog 
from Q
group by Q.ApplTrackingNo

The messages and formats must be adjusted.

Answer 2

Version: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)

This is an outdated build of SQL 2016, and you should install Service Pack3 and then apply CU1 to SP3 to be on the final version of SQL 2016.

I'm afraid there are still too many uncertainties about your problem. There are two tables, and both have multiples rows for the same (ParentTblRowId, ApplTrackingNo], and there is nothing else that apparently say which rows in table one goes together with which row in table two. The CreatedOn column is close, but the dates are out of sync.

What I can give you is the general pattern for how to build a delimited list from multiple rows in older versions of SQL Server. Here is a query that works against the catalog views in SQL Server to give you a list of the columns in all tables:

SELECT s.name AS "Schema", t.name AS "Table",
       substring(c.collist, 1, len(c.collist) - 2) AS "Columns"
FROM   sys.schemas s 
JOIN   sys.tables t ON s.schema_id = t.schema_id
CROSS  APPLY (SELECT c.name + ' | '
              FROM   sys.columns c
              WHERE  t.object_id = c.object_id
              ORDER  BY c.name
              FOR XML PATH('')) AS c(collist)
ORDER  BY s.name, t.name