Please, consider the following scenario. There are 2 tables: Core and History. Core looks as follows

History looks as follows

History holds a list of cntSerialNumber changes through the time The following script that gives the user SerialNumber as it was at the beginning and the end of a certain period

    SELECT Convert(date,[HWDateStart]) 
      ,Convert(date,[HWDateEnd]) 
      ,[HWQueueID]
      , HS.cntSerialNumber
      , HE.cntSerialNumber
  FROM [watchdocstats].[dbo].[tblJT] SNTarget,
  [watchdocstats].[dbo].[tblQueueByConfig] HS,
  [watchdocstats].[dbo].[tblQueueByConfig] HE
    WHERE 
        SNTarget.HWQueueID      = HS.cntQueueID
  AND   SNTarget.HWQueueID      = HE.cntQueueID
  AND   Convert(date,SNTarget.HWDateStart) = Convert(date,HS.cntDatetime)
  AND   Convert(date,SNTarget.HWDateEnd) = Convert(date,HE.cntDatetime)

But what if SerialNumber has been changed more than once down the road Is there a way to select a table that would show commaseparated SerialNumbers in cntSerialNumber column? Smth, like

CodePudding user response：

Depending on your DBMS, you should be able to use the LISTAGG function along with a GROUP BY clause