Need a help in writing complex sql query even though I was writing queries for very long time, some how this looks complex

1

Please find the below input table and the relationships

  1. A package is associated to a shipment and a shipment is put into a box.
  2. Group of packages also can be associated to a single shipment and each package can be put into different boxes.

Now if we look at the input table below at a shipment level

S1 is placed in B1 and B2.
S2 is placed in B2 and B3.
S3 is placed in B3 and B4.
and this can go on and at the same pattern, however it will get cut at a point.
S4 is placed into B4.

Now the above data I wanted to make to a group, so that I can move all the boxes together.
There could be a case where single package can go into single shipment and single box example in the case below of P10

Package Number Shipment Number Box Number
P1 S1 B1
P2 S1 B2
P3 S2 B2
P4 S2 B3
P5 S3 B3
P6 S3 B4
P7 S4 B4
P8 S5 B5
P9 S5 B6
P10 S6 B7

I would like to get two outputs
Output 1 : which has additional columns Group Number and Group Name

Package Number Shipment Number Box Number Group Number Group Name
P1 S1 B1 1 G1
P2 S1 B2 1 G1
P3 S2 B2 1 G1
P4 S2 B3 1 G1
P5 S3 B3 1 G1
P6 S3 B4 1 G1
P7 S4 B4 1 G1
P8 S5 B5 2 G2
P9 S5 B6 2 G2
P10 S6 B7 3 G3

Output 2 : which is grouped at a Box level and having Box linkage data

Box Number Group Number Group Name Box Link
B1 1 G1 B2,B3,B4
B2 1 G1 B1,B3,B4
B3 1 G1 B1,B2,B4
B4 1 G1 B1,B2,B3
B5 2 G2 B6
B6 2 G2 B5
B7 3 G3

I tried making self joins, product joins by creating two different tables at shipment level,box level somehow couldn't make it .

2

You are more likely to get a reply if you provide consumable test data:

CREATE TABLE #t
(
	PackageNum varchar(10) NOT NULL PRIMARY KEY
	,ShipmentNum varchar(10) NOT NULL
	,BoxNum varchar(10) NOT NULL
);
INSERT INTO #t
VALUES ('P1', 'S1', 'B1')
	,('P2', 'S1', 'B2')
	,('P3', 'S2', 'B2')
	,('P4', 'S2', 'B3')
	,('P5', 'S3', 'B3')
	,('P6', 'S3', 'B4')
	,('P7', 'S4', 'B4')
	,('P8', 'S5', 'B5')
	,('P9', 'S5', 'B6')
	,('P10', 'S6', 'B7');

Your test data does not make it entirely clear what you want.

The first result looks like an 'Islands and Gaps' problem. There are lots of articles and solutions for this. eg

WITH Boundaries
AS
(
	SELECT PackageNum, ShipmentNum, BoxNum
		,CASE
			WHEN ShipmentNum = LAG(ShipmentNum) OVER (ORDER BY ShipmentNum, BoxNum)
			THEN 0
			WHEN ShipmentNum > LAG(ShipmentNum) OVER (ORDER BY ShipmentNum, BoxNum)
				AND BoxNum = LAG(BoxNum) OVER (ORDER BY ShipmentNum, BoxNum)
			THEN 0
			ELSE 1
		END AS Boundary
	FROM #t
)
SELECT PackageNum, ShipmentNum, BoxNum
	,SUM(Boundary) OVER (ORDER BY ShipmentNum, BoxNum) AS GroupNum
	,'G' + CAST(SUM(Boundary) OVER (ORDER BY ShipmentNum, BoxNum) AS varchar(10)) AS GroupName
FROM Boundaries
ORDER BY ShipmentNum, BoxNum;

Your second result seems to build on the first result to string aggregate the box numbers and then remove the box for the current row.

For SQL2017 and above the STRING_AGG() function can be used. Pre-SQL2017 use FOR XML:

WITH Boundaries
AS
(
	SELECT PackageNum, ShipmentNum, BoxNum
		,CASE
			WHEN ShipmentNum = LAG(ShipmentNum) OVER (ORDER BY ShipmentNum, BoxNum)
			THEN 0
			WHEN ShipmentNum > LAG(ShipmentNum) OVER (ORDER BY ShipmentNum, BoxNum)
				AND BoxNum = LAG(BoxNum) OVER (ORDER BY ShipmentNum, BoxNum)
			THEN 0
			ELSE 1
		END AS Boundary
	FROM #t
)
,Grps
AS
(
	SELECT DISTINCT BoxNum
		,SUM(Boundary) OVER (ORDER BY ShipmentNum, BoxNum) AS GroupNum
	FROM Boundaries
)
,GrpAgg
AS
(
	SELECT GroupNum, STRING_AGG(BoxNum, ', ') + ', ' AS BoxAgg
	FROM Grps
	GROUP BY GroupNum
)
/* Pre-SQL2017
,GrpAgg
AS
(
	SELECT GroupNum
		,(
			SELECT G2.BoxNum + ', '
			FROM Grps G2
			WHERE G2.GroupNum = G1.GroupNum
			FOR XML PATH, TYPE
		).value(N'.[1]', N'varchar(max)') AS BoxAgg
	FROM Grps G1
	GROUP BY GroupNum
)
*/
SELECT G.BoxNum, G.GroupNum
	,'G' + CAST(G.GroupNum AS varchar(10)) AS GroupName
	,CASE
		WHEN LEN(X.BoxLink) = 0
		THEN ''
		ELSE LEFT(X.BoxLink, LEN(X.BoxLink) - 1)
	END AS BoxLink
FROM Grps G
	JOIN GrpAgg A
		ON G.GroupNum = A.GroupNum
	CROSS APPLY
	(
		VALUES( REPLACE(A.BoxAgg, G.BoxNum + ', ', '') )
	) X (BoxLink);