How to concatenate text from multiple rows into a single text string in SQL Server with condition

Question

I have this table like this

RowID	Model	Request No	Resource	Date	Time	Duration
12	W221A0	RE-1716	RG	14-Jan-22	9:50:40	4
14	X210132	RE-1341	CL	15-Dec-21	10:45:09	2
20	X210132	RE-1341	ZA	17-Dec-21	8:57:09	5
30	X210132	RE-1341	SA	21-Dec-21	8:10:02	1
44	W221A0	RE-1716	RG	10-Jan-22	10:14:40	8
59	X210132	RE-1341	ZA	16-Dec-21	9:56:46	3
60	X210132	RE-1341	SA	20-Dec-21	8:30:50	0
95	X210132	RE-1341	ZA	21-Dec-21	10:12:51	6
99	W221A0	RE-1716	RG	11-Jan-22	8:06:46	2
100	X210132	RE-1341	ZA	15-Dec-21	10:20:26	12
102	W221A0	RE-1716	CL	14-Jan-22	8:10:27	3
105	W221A0	RE-1716	TE	10-Jan-22	8:36:33	7
106	W221A0	RE-1716	RG	6-Jan-22	10:29:44	5
300	W221A0	RE-1716	RG	14-Jan-22	9:01:18	6

After sorting by Date, Time field. I expect to have new table with Like this:

The sequence of the table is important to determine the grouping by Resource value, the Resources that are sequentially next to each other should be collapsed together to build the STEP and STEP TIME, where the STEP TIME value for each resource is the SUM of the Duration values for that Resource.

These two images should illustrate how the computed values are constructed:

I tried use

     SELECT Main.[Request No.],LEFT(Main.STORAGEDATA,Len(Main.STORAGEDATA)-1) As "Process" FROM(
    
      SELECT DISTINCT ST2.[Request No.], 
        (
            SELECT ST1.Resource + ',' AS [text()]
            FROM dbo.STORAGEDATA ST1
            WHERE ST1.[Request No.] = ST2.[Request No.]
            ORDER BY ST1.[Request No.]
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)') STORAGEDATA
    FROM dbo.STORAGEDATA ST2
) [Main]

However, It doesn't return what I expect, this is the output:

Request No.	Process
RE-1341	CL,ZA,SA,ZA,SA,ZA,ZA
RE-1716	RG,RG,RG,CL,TE,RG,RG

---

This CREATE TABLE expression can be used for this dataset:

CREATE TABLE STORAGEDATA (
    RowID int,
    Model varchar(10),
    [Request No.] varchar(10),
    [Resource] char(2),
    [Date] Date,
    [Time] Time,
    Duration int
    )
INSERT INTO STORAGEDATA
VALUES
 ( 12   ,'W221A0 ','RE-1716','RG','14-Jan-22','9:50:40  ',4  )
,( 14   ,'X210132','RE-1341','CL','15-Dec-21','10:45:09 ',2  )
,( 20   ,'X210132','RE-1341','ZA','17-Dec-21','8:57:09  ',5  )
,( 30   ,'X210132','RE-1341','SA','21-Dec-21','8:10:02  ',1  )
,( 44   ,'W221A0 ','RE-1716','RG','10-Jan-22','10:14:40 ',8  )
,( 59   ,'X210132','RE-1341','ZA','16-Dec-21','9:56:46  ',3  )
,( 60   ,'X210132','RE-1341','SA','20-Dec-21','8:30:50  ',0  )
,( 95   ,'X210132','RE-1341','ZA','21-Dec-21','10:12:51 ',6  )
,( 99   ,'W221A0 ','RE-1716','RG','11-Jan-22','8:06:46  ',2  )
,( 100  ,'X210132','RE-1341','ZA','15-Dec-21','10:20:26 ',12 )
,( 102  ,'W221A0 ','RE-1716','CL','14-Jan-22','8:10:27  ',3  )
,( 105  ,'W221A0 ','RE-1716','TE','10-Jan-22','8:36:33  ',7  )
,( 106  ,'W221A0 ','RE-1716','RG','6-Jan-22 ','10:29:44 ',5  )
,( 300  ,'W221A0 ','RE-1716','RG','14-Jan-22','9:01:18  ',6  );

Answer 1

This solution uses Gap and Island analysis to identify the main resource grouping, by then uses STRING_AGG to generate the Step and Step Time columns and then joins those results back onto the original set.

;WITH LagData as (
    SELECT *, LAG(Resource,1) OVER(PARTITION BY Model, [Request No.] ORDER BY Date,Time) LG
    FROM STORAGEDATA ST2
), IslandIds as (
    SELECT *, 
    SUM(CASE WHEN LG IS NULL OR Resource <> LG THEN 1 ELSE 0 END)  OVER(PARTITION BY [Request No.] ORDER BY Date,TIme) IslandId
    FROM LagData
), Islands as (
  SELECT TOP 100 PERCENT Model, [Resource], IslandId, SUM(Duration) as TotalDuration 
  FROM IslandIds 
  GROUP BY IslandId, Model, [Resource]
  ORDER BY IslandId
)
, ModelSummary as (
    SELECT Model
          ,STRING_AGG([Resource], '*') as Step
          ,STRING_AGG(TotalDuration, ',') as [Step Time]
      FROM Islands 
      GROUP BY Model
)
SELECT main.Model,main.[Request No.],main.[Resource],main.[Date],main.[Time],main.Duration
     , ModelSummary.Step
     , ModelSummary.[Step Time]
FROM IslandIds as main
INNER JOIN ModelSummary ON ModelSummary.Model = main.Model
ORDER BY main.Model DESC, [Date], [Time]

NOTE: Special acknowledgement to the solution by @JMabee, this solution was edited to use the simple Island only analysis, as we are not analysing the gaps here.

This solution is still different in the way the string aggregation is evaluated.

The TOP 100 PERCENT is a bit of a hack to ensure the correct sequence for the STRING_AGG

The setup I used for this was:

CREATE TABLE STORAGEDATA (
    RowID int,
    Model varchar(10),
    [Request No.] varchar(10),
    [Resource] char(2),
    [Date] Date,
    [Time] Time,
    Duration int
    )
INSERT INTO STORAGEDATA
VALUES
 ( 12   ,'W221A0 ','RE-1716','RG','14-Jan-22','9:50:40  ',4  )
,( 14   ,'X210132','RE-1341','CL','15-Dec-21','10:45:09 ',2  )
,( 20   ,'X210132','RE-1341','ZA','17-Dec-21','8:57:09  ',5  )
,( 30   ,'X210132','RE-1341','SA','21-Dec-21','8:10:02  ',1  )
,( 44   ,'W221A0 ','RE-1716','RG','10-Jan-22','10:14:40 ',8  )
,( 59   ,'X210132','RE-1341','ZA','16-Dec-21','9:56:46  ',3  )
,( 60   ,'X210132','RE-1341','SA','20-Dec-21','8:30:50  ',0  )
,( 95   ,'X210132','RE-1341','ZA','21-Dec-21','10:12:51 ',6  )
,( 99   ,'W221A0 ','RE-1716','RG','11-Jan-22','8:06:46  ',2  )
,( 100  ,'X210132','RE-1341','ZA','15-Dec-21','10:20:26 ',12 )
,( 102  ,'W221A0 ','RE-1716','CL','14-Jan-22','8:10:27  ',3  )
,( 105  ,'W221A0 ','RE-1716','TE','10-Jan-22','8:36:33  ',7  )
,( 106  ,'W221A0 ','RE-1716','RG','6-Jan-22 ','10:29:44 ',5  )
,( 300  ,'W221A0 ','RE-1716','RG','14-Jan-22','9:01:18  ',6  );

Answer 2

This is one way to do it, I am definitely interested to see how other folks solve this one. It appears to be a Gaps and Islands problem, so the CTEs are used to create a Group using the Resource within the Request No.

The second part uses subqueries to generate the distinct Resource and also the SUM of the duration within the Group as lists.

CREATE TABLE #tmp(RowID int,Model varchar(10), [Request No.] varchar(10),[Resource] varchar(10), [Date] Date, [Time] Time, Duration int );
INSERT INTO #tmp VALUES
( 12 ,'W221A0' ,'RE-1716','RG','14-Jan-22','9:50:40' ,4),
( 14 ,'X210132','RE-1341','CL','15-Dec-21','10:45:09',2),
( 20 ,'X210132','RE-1341','ZA','17-Dec-21','8:57:09' ,5),
( 30 ,'X210132','RE-1341','SA','21-Dec-21','8:10:02' ,1),
( 44 ,'W221A0' ,'RE-1716','RG','10-Jan-22','10:14:40',8),
( 59 ,'X210132','RE-1341','ZA','16-Dec-21','9:56:46' ,3),
( 60 ,'X210132','RE-1341','SA','20-Dec-21','8:30:50' ,0),
( 95 ,'X210132','RE-1341','ZA','21-Dec-21','10:12:51',6),
( 99 ,'W221A0' ,'RE-1716','RG','11-Jan-22','8:06:46' ,2),
( 100,'X210132','RE-1341','ZA','15-Dec-21','10:20:26',12),
( 102,'W221A0' ,'RE-1716','CL','14-Jan-22','8:10:27' ,3),
( 105,'W221A0' ,'RE-1716','TE','10-Jan-22','8:36:33' ,7),
( 106,'W221A0' ,'RE-1716','RG','6-Jan-22 ','10:29:44',5),
( 300,'W221A0' ,'RE-1716','RG','14-Jan-22','9:01:18' ,6);

WITH CTE AS
(
SELECT *, LAG(Resource,1) OVER(PARTITION BY [Request No.] ORDER BY Date,Time) LG
FROM #tmp ST2
),CTE2 AS
(
    SELECT *, 
    SUM(CASE WHEN LG IS NULL OR Resource <> LG THEN 1 ELSE 0 END)  OVER(PARTITION BY [Request No.] ORDER BY Date,TIme) GRP
    FROM CTE
)
SELECT RowID, Model,[Request No.], Resource,[Date],[Time],Duration,
        STUFF((SELECT TOP 1 WITH TIES '*' + ST1.Resource AS [text()]
            FROM CTE2 ST1
            WHERE ST1.[Request No.] = ST2.[Request No.]
            ORDER BY ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY  Date,Time)
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'),1,1,'') Step,

        STUFF((SELECT  '-' + CAST(SUM(Duration)  as varchar(5))  AS [text()]
            FROM CTE2 ST1
            WHERE ST1.[Request No.] = ST2.[Request No.]
            GROUP BY Grp
            ORDER BY GRP
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'),1,1,'') StepTime
FROM CTE2 ST2
ORDER BY Date, Time