Query to get recursive records based on first ID-CodePudding

I am trying to get the first spot for a given Lot, and how many other spots were refurbished to that first spot. My table is :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Spots](
[SpotID] [int] NOT NULL,
[LotID] [int] NULL,
[RefurbedSpotID] [int] NULL,
[datestamp] [datetime] NULL,
 CONSTRAINT [PK_Spots] PRIMARY KEY CLUSTERED 
(
    [SpotID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (178, 5, 
NULL, CAST(N'2022-08-30T12:41:50.833' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (183, 5, 
178, CAST(N'2022-08-30T12:42:01.263' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (193, 5, 
183, CAST(N'2022-08-30T12:43:03.527' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (212, 5, 
NULL, CAST(N'2022-08-30T12:43:12.373' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (213, 5, 
193, CAST(N'2022-08-30T12:43:19.023' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (380, 70, 
NULL, CAST(N'2022-08-30T12:43:36.837' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (381, 70, 
380, CAST(N'2022-08-30T12:43:43.637' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (382, 70, 
NULL, CAST(N'2022-08-30T12:43:54.780' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (383, 70, 
381, CAST(N'2022-08-30T12:44:06.390' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (384, 70, 
383, CAST(N'2022-08-30T12:44:13.800' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (385, 71, 
NULL, CAST(N'2022-08-30T12:46:17.660' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (389, 70, 
NULL, CAST(N'2022-08-30T12:44:17.070' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (398, 71, 
NULL, CAST(N'2022-08-30T12:46:26.670' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (465, 
101, NULL, CAST(N'2022-08-30T12:46:44.450' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (491, 
118, NULL, CAST(N'2022-08-30T12:48:32.380' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1496, 
 118, 491, CAST(N'2022-08-30T12:48:38.497' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1497, 
118, NULL, CAST(N'2022-08-30T12:48:42.617' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1498, 
118, 1496, CAST(N'2022-08-30T12:48:49.290' AS DateTime))
GO
 INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1633, 
70, 382, CAST(N'2022-08-30T12:44:24.183' AS DateTime))
GO
ALTER TABLE [dbo].[Spots] ADD  CONSTRAINT [DF_Spots_datestamp]  DEFAULT (getdate()) FOR 
[datestamp]
gO

This is the expected result. First SpotIDs are in brackets.

for LotID : 5

213, 193, 183,  [ 178 ]

for LotID : 70 x

384, 383, 381, [ 380 ]

for LotID : 71 x

[ 385 ]

for LotID : 101 x

[ 465 ]

for LotID : 118

1498, 1496,  [ 491 ]

What I tried was :

select *
from ( 
    select s2.spotID, s2.lotID , s2.RefurbedSpotID  
    from spots s2
    where s2.lotID = @lotID
    and s2.RefurbedSpotID is not null 

    union 

    select s1.spotID, s1.lotID, s1.RefurbedSpotID  
    from spots s1
    inner join ( select s2.spotID as s2_SpotID, s2.lotID as s2_lotID, s2.RefurbedSpotID 
as s2_RefurbedSpotID
                 from spots s2
                 where s2.lotID = @lotID ) s3 on s2_RefurbedSpotID = s1.spotID 
    where s1.lotID = @lotID 
    ) q
 order by spotID

However, I don't get the correct results for LotIDs 70, 71, and 101. Any suggestions?

CodePudding user response：

You then need a recursive CTE for this. Start with the first row for each LotId and calculate a Level column along with the recursion.

Then you simply aggregate using STRING_AGG with a WITHIN GROUP (ORDER BY clause

WITH cte AS (
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(CONCAT('[ ', s.SpotId, ' ]') AS varchar(30)),
      Level = 1
    FROM (
        SELECT *,
          rn = ROW_NUMBER() OVER (PARTITION BY s.LotID ORDER BY s.datestamp)
        FROM Spots s
    ) s
    WHERE s.rn = 1
    
    UNION ALL
    
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(s.SpotId AS varchar(30)),
      Level = cte.Level   1
    FROM cte
    JOIN Spots s ON s.RefurbedSpotID = cte.SpotID AND s.LotID = cte.LotID
)
SELECT
  cte.LotID,
  STRING_AGG(SpotIdString, ', ') WITHIN GROUP (ORDER BY cte.Level DESC)
FROM cte
GROUP BY
  cte.LotID;

Another option is to calculate the string during the recursion, and then just filter to the last one of the recursion using ROW_NUMBER

WITH cte AS (
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(CONCAT('[ ', s.SpotId, ' ]') AS varchar(4000)),
      Level = 1
    FROM (
        SELECT *,
          rn = ROW_NUMBER() OVER (PARTITION BY s.LotID ORDER BY s.datestamp)
        FROM Spots s
    ) s
    WHERE s.rn = 1
    
    UNION ALL
    
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(CONCAT(s.SpotID, ', ', cte.SpotIdString) AS varchar(4000)),
      Level = cte.Level   1
    FROM cte
    JOIN Spots s ON s.RefurbedSpotID = cte.SpotID AND s.LotID = cte.LotID
)
SELECT
  cte.LotID,
  cte.SpotIdString
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY cte.LotID ORDER BY cte.Level DESC)
    FROM cte
) cte
WHERE cte.rn = 1;

db<>fiddle