SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

/*  Data setup  - note additional orders  */
CREATE TABLE #Order (cust_id int, pack_id varchar(1), item_id int, qty int, PRIMARY KEY (cust_id, pack_id, item_id))
INSERT INTO #Order (cust_id, pack_id, item_id, qty) VALUES
(1, 'A', 1, 1),
(1, 'A', 2, 1),
(1, 'A', 3, 4),
(1, 'A', 4, 0),
(1, 'A', 5, 0),
(2, 'A', 1, 21),
(2, 'A', 2, 21),
(2, 'A', 3, 0),
(2, 'A', 4, 24),
(2, 'A', 5, 0),
(2, 'B', 1, 2),
(2, 'B', 2, 3),
(2, 'B', 3, 4),
(2, 'B', 4, 5),
(2, 'B', 5, 6);
15 rows affected

/*  Pivot results  */

WITH Nums(n) AS
    (SELECT (c * 100) + (b * 10) + (a) + 1 AS n
        FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) A(a)
            CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) B(b)
            CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) C(c)
    ),
ItemBatches AS
(SELECT cust_id, pack_id, item_id, 
        FLOOR((ROW_NUMBER() OVER (PARTITION BY cust_id, pack_id ORDER BY item_id, N.n)-1) / 5) + 1 AS rn_batch
    FROM #Order O
        INNER JOIN Nums N ON N.n <= O.qty
)
SELECT * 
FROM (SELECT cust_id, pack_id, rn_batch, 'Item_' + LTRIM(STR(item_id)) AS item_desc 
     FROM ItemBatches
     ) src
PIVOT
    (COUNT(item_desc) FOR item_desc IN ([Item_1], [Item_2], [Item_3], [Item_4], [Item_5])) pvt
ORDER BY cust_id, pack_id, rn_batch;


cust_id
pack_id
rn_batch
Item_1
Item_2
Item_3
Item_4
Item_5


1
A
1
1
1
3
0
0

1
A
2
0
0
1
0
0

2
A
1
5
0
0
0
0

2
A
2
5
0
0
0
0

2
A
3
5
0
0
0
0

2
A
4
5
0
0
0
0

2
A
5
1
4
0
0
0

2
A
6
0
5
0
0
0

2
A
7
0
5
0
0
0

2
A
8
0
5
0
0
0

2
A
9
0
2
0
3
0

2
A
10
0
0
0
5
0

2
A
11
0
0
0
5
0

2
A
12
0
0
0
5
0

2
A
13
0
0
0
5
0

2
A
14
0
0
0
1
0

2
B
1
2
3
0
0
0

2
B
2
0
0
4
1
0

2
B
3
0
0
0
4
1

2
B
4
0
0
0
0
5


/*  EXTENDED VERSION - showing sub-steps  */

-- Creates a table with numbers 1 to 1000

CREATE TABLE #nums(n int PRIMARY KEY);
INSERT INTO #nums(n)
    SELECT (c * 100) + (b * 10) + (a) + 1 AS n
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) A(a)
        CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) B(b)
        CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) C(c);    
		
1000 rows affected

-- Splits the #Orders table into 1 row per item, and puts them into rn_batches

SELECT	cust_id, pack_id, item_id, 
	FLOOR((ROW_NUMBER() OVER (PARTITION BY cust_id, pack_id ORDER BY item_id, N.n)-1) / 5) + 1 AS rn_batch
INTO	#ItemBatches
FROM	#Order O
	INNER JOIN #nums N ON N.n <= O.qty;

SELECT * FROM #ItemBatches;


cust_id
pack_id
item_id
rn_batch


1
A
1
1

1
A
2
1

1
A
3
1

1
A
3
1

1
A
3
1

1
A
3
2

2
A
1
1

2
A
1
1

2
A
1
1

2
A
1
1

2
A
1
1

2
A
1
2

2
A
1
2

2
A
1
2

2
A
1
2

2
A
1
2

2
A
1
3

2
A
1
3

2
A
1
3

2
A
1
3

2
A
1
3

2
A
1
4

2
A
1
4

2
A
1
4

2
A
1
4

2
A
1
4

2
A
1
5

2
A
2
5

2
A
2
5

2
A
2
5

2
A
2
5

2
A
2
6

2
A
2
6

2
A
2
6

2
A
2
6

2
A
2
6

2
A
2
7

2
A
2
7

2
A
2
7

2
A
2
7

2
A
2
7

2
A
2
8

2
A
2
8

2
A
2
8

2
A
2
8

2
A
2
8

2
A
2
9

2
A
2
9

2
A
4
9

2
A
4
9

2
A
4
9

2
A
4
10

2
A
4
10

2
A
4
10

2
A
4
10

2
A
4
10

2
A
4
11

2
A
4
11

2
A
4
11

2
A
4
11

2
A
4
11

2
A
4
12

2
A
4
12

2
A
4
12

2
A
4
12

2
A
4
12

2
A
4
13

2
A
4
13

2
A
4
13

2
A
4
13

2
A
4
13

2
A
4
14

2
B
1
1

2
B
1
1

2
B
2
1

2
B
2
1

2
B
2
1

2
B
3
2

2
B
3
2

2
B
3
2

2
B
3
2

2
B
4
2

2
B
4
3

2
B
4
3

2
B
4
3

2
B
4
3

2
B
5
3

2
B
5
4

2
B
5
4

2
B
5
4

2
B
5
4

2
B
5
4


-- Do the pivot

SELECT * 
FROM (SELECT cust_id, 
             pack_id, 
             rn_batch, 
             'Item_' + LTRIM(STR(item_id)) AS item_desc 
     FROM #ItemBatches
     ) src
PIVOT
    (COUNT(item_desc) 
    FOR item_desc IN 
      ([Item_1], [Item_2], [Item_3], [Item_4], [Item_5])
    ) pvt;
	


cust_id
pack_id
rn_batch
Item_1
Item_2
Item_3
Item_4
Item_5


1
A
1
1
1
3
0
0

1
A
2
0
0
1
0
0

2
A
1
5
0
0
0
0

2
A
2
5
0
0
0
0

2
A
3
5
0
0
0
0

2
A
4
5
0
0
0
0

2
A
5
1
4
0
0
0

2
A
6
0
5
0
0
0

2
A
7
0
5
0
0
0

2
A
8
0
5
0
0
0

2
A
9
0
2
0
3
0

2
A
10
0
0
0
5
0

2
A
11
0
0
0
5
0

2
A
12
0
0
0
5
0

2
A
13
0
0
0
5
0

2
A
14
0
0
0
1
0

2
B
1
2
3
0
0
0

2
B
2
0
0
4
1
0

2
B
3
0
0
0
4
1

2
B
4
0
0
0
0
5

cust_id	pack_id	rn_batch	Item_1	Item_2	Item_3	Item_4	Item_5
1	A	1	1	1	3	0	0
1	A	2	0	0	1	0	0
2	A	1	5	0	0	0	0
2	A	2	5	0	0	0	0
2	A	3	5	0	0	0	0
2	A	4	5	0	0	0	0
2	A	5	1	4	0	0	0
2	A	6	0	5	0	0	0
2	A	7	0	5	0	0	0
2	A	8	0	5	0	0	0
2	A	9	0	2	0	3	0
2	A	10	0	0	0	5	0
2	A	11	0	0	0	5	0
2	A	12	0	0	0	5	0
2	A	13	0	0	0	5	0
2	A	14	0	0	0	1	0
2	B	1	2	3	0	0	0
2	B	2	0	0	4	1	0
2	B	3	0	0	0	4	1
2	B	4	0	0	0	0	5

cust_id	pack_id	item_id	rn_batch
1	A	1	1
1	A	2	1
1	A	3	1
1	A	3	1
1	A	3	1
1	A	3	2
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	5
2	A	2	5
2	A	2	5
2	A	2	5
2	A	2	5
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	9
2	A	2	9
2	A	4	9
2	A	4	9
2	A	4	9
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	14
2	B	1	1
2	B	1	1
2	B	2	1
2	B	2	1
2	B	2	1
2	B	3	2
2	B	3	2
2	B	3	2
2	B	3	2
2	B	4	2
2	B	4	3
2	B	4	3
2	B	4	3
2	B	4	3
2	B	5	3
2	B	5	4
2	B	5	4
2	B	5	4
2	B	5	4
2	B	5	4

cust_id	pack_id	rn_batch	Item_1	Item_2	Item_3	Item_4	Item_5
1	A	1	1	1	3	0	0
1	A	2	0	0	1	0	0
2	A	1	5	0	0	0	0
2	A	2	5	0	0	0	0
2	A	3	5	0	0	0	0
2	A	4	5	0	0	0	0
2	A	5	1	4	0	0	0
2	A	6	0	5	0	0	0
2	A	7	0	5	0	0	0
2	A	8	0	5	0	0	0
2	A	9	0	2	0	3	0
2	A	10	0	0	0	5	0
2	A	11	0	0	0	5	0
2	A	12	0	0	0	5	0
2	A	13	0	0	0	5	0
2	A	14	0	0	0	1	0
2	B	1	2	3	0	0	0
2	B	2	0	0	4	1	0
2	B	3	0	0	0	4	1
2	B	4	0	0	0	0	5

cust_id	pack_id	rn_batch	Item_1	Item_2	Item_3	Item_4	Item_5
1	A	1	1	1	3	0	0
1	A	2	0	0	1	0	0
2	A	1	5	0	0	0	0
2	A	2	5	0	0	0	0
2	A	3	5	0	0	0	0
2	A	4	5	0	0	0	0
2	A	5	1	4	0	0	0
2	A	6	0	5	0	0	0
2	A	7	0	5	0	0	0
2	A	8	0	5	0	0	0
2	A	9	0	2	0	3	0
2	A	10	0	0	0	5	0
2	A	11	0	0	0	5	0
2	A	12	0	0	0	5	0
2	A	13	0	0	0	5	0
2	A	14	0	0	0	1	0
2	B	1	2	3	0	0	0
2	B	2	0	0	4	1	0
2	B	3	0	0	0	4	1
2	B	4	0	0	0	0	5

cust_id	pack_id	item_id	rn_batch
1	A	1	1
1	A	2	1
1	A	3	1
1	A	3	1
1	A	3	1
1	A	3	2
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	5
2	A	2	5
2	A	2	5
2	A	2	5
2	A	2	5
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	9
2	A	2	9
2	A	4	9
2	A	4	9
2	A	4	9
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	14
2	B	1	1
2	B	1	1
2	B	2	1
2	B	2	1
2	B	2	1
2	B	3	2
2	B	3	2
2	B	3	2
2	B	3	2
2	B	4	2
2	B	4	3
2	B	4	3
2	B	4	3
2	B	4	3
2	B	5	3
2	B	5	4
2	B	5	4
2	B	5	4
2	B	5	4
2	B	5	4

cust_id	pack_id	rn_batch	Item_1	Item_2	Item_3	Item_4	Item_5
1	A	1	1	1	3	0	0
1	A	2	0	0	1	0	0
2	A	1	5	0	0	0	0
2	A	2	5	0	0	0	0
2	A	3	5	0	0	0	0
2	A	4	5	0	0	0	0
2	A	5	1	4	0	0	0
2	A	6	0	5	0	0	0
2	A	7	0	5	0	0	0
2	A	8	0	5	0	0	0
2	A	9	0	2	0	3	0
2	A	10	0	0	0	5	0
2	A	11	0	0	0	5	0
2	A	12	0	0	0	5	0
2	A	13	0	0	0	5	0
2	A	14	0	0	0	1	0
2	B	1	2	3	0	0	0
2	B	2	0	0	4	1	0
2	B	3	0	0	0	4	1
2	B	4	0	0	0	0	5

cust_id	pack_id	rn_batch	Item_1	Item_2	Item_3	Item_4	Item_5
1	A	1	1	1	3	0	0
1	A	2	0	0	1	0	0
2	A	1	5	0	0	0	0
2	A	2	5	0	0	0	0
2	A	3	5	0	0	0	0
2	A	4	5	0	0	0	0
2	A	5	1	4	0	0	0
2	A	6	0	5	0	0	0
2	A	7	0	5	0	0	0
2	A	8	0	5	0	0	0
2	A	9	0	2	0	3	0
2	A	10	0	0	0	5	0
2	A	11	0	0	0	5	0
2	A	12	0	0	0	5	0
2	A	13	0	0	0	5	0
2	A	14	0	0	0	1	0
2	B	1	2	3	0	0	0
2	B	2	0	0	4	1	0
2	B	3	0	0	0	4	1
2	B	4	0	0	0	0	5

cust_id	pack_id	item_id	rn_batch
1	A	1	1
1	A	2	1
1	A	3	1
1	A	3	1
1	A	3	1
1	A	3	2
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	1
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	2
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	3
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	4
2	A	1	5
2	A	2	5
2	A	2	5
2	A	2	5
2	A	2	5
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	6
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	7
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	8
2	A	2	9
2	A	2	9
2	A	4	9
2	A	4	9
2	A	4	9
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	10
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	11
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	12
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	13
2	A	4	14
2	B	1	1
2	B	1	1
2	B	2	1
2	B	2	1
2	B	2	1
2	B	3	2
2	B	3	2
2	B	3	2
2	B	3	2
2	B	4	2
2	B	4	3
2	B	4	3
2	B	4	3
2	B	4	3
2	B	5	3
2	B	5	4
2	B	5	4
2	B	5	4
2	B	5	4
2	B	5	4

cust_id	pack_id	rn_batch	Item_1	Item_2	Item_3	Item_4	Item_5
1	A	1	1	1	3	0	0
1	A	2	0	0	1	0	0
2	A	1	5	0	0	0	0
2	A	2	5	0	0	0	0
2	A	3	5	0	0	0	0
2	A	4	5	0	0	0	0
2	A	5	1	4	0	0	0
2	A	6	0	5	0	0	0
2	A	7	0	5	0	0	0
2	A	8	0	5	0	0	0
2	A	9	0	2	0	3	0
2	A	10	0	0	0	5	0
2	A	11	0	0	0	5	0
2	A	12	0	0	0	5	0
2	A	13	0	0	0	5	0
2	A	14	0	0	0	1	0
2	B	1	2	3	0	0	0
2	B	2	0	0	4	1	0
2	B	3	0	0	0	4	1
2	B	4	0	0	0	0	5