How to add dynamic field for each child item?

Question

I work for a shipment company. One type of item we ship are these boxes (which we call a "kit") and we can place various individual items into this "kit". Each item contains its own unique barcode plus the kit itself contains a barcode.

I need to create a report containing one line for each "kit". Each line will have the barcode's of all items within the kit. So, for example, in the below scenario, "Kit A" has 2 items in it. "Kit B" has 1 item in it:

Kit ID | Kit Name | Master Barcode | Item 1 | Item 1 Barcode | Item 2 | Item 2 Barcode

13 | Kit A | 38829199299 | Item 1a | Child4838822 | Item 1b | Child42292001

67 | Kit B | 20010119229 | Item 2a | Child7474272 | (blank) | (blank)

I have 2 tables to handle our Kits:

dbo.Kits

dbo.KitMap

Kits:

kit_id | kit_parent_id | name

13 | null | Kit A

14 | 13 | Item 1a

15 | 13 | Item 1b

67 | null | Kit B

68 | 67 | Item 2a

KitMap

kit_map_id | kit_id | parent_id | barcode

100 | 13 | null | 38829199299

101 | 14 | 100 | Child4838822

102 | 15 | 100 | Child42292001

103 | 67 | null | 20010119229

104 | 68 | 103 | Child7474272

Based on that data, how can I create a report as shown above? Thanks for any help you can provide

Also, here's a query to generate the tables, if it helps. Thanks

DECLARE @kits TABLE (kit_id INT, kit_parent_id INT, NAME VARCHAR(255))

INSERT INTO @kits (kit_id, kit_parent_id, NAME) VALUES 
    (13, NULL, 'Kit A'), 
    (14, 13, 'Item 1a'), 
    (15, 13, 'Item 1b'), 
    (67, NULL, 'Kit B'), 
    (68, 67, 'Item 2a')

DECLARE @KitMap TABLE (kit_map_id INT, kit_id INT, parent_id INT, barcode VARCHAR(255))

INSERT INTO @KitMap (kit_map_id, kit_id, parent_id, barcode) VALUES 
    (100, 13, NULL, '38829199299'), 
    (101, 14, 100, 'Child4838822'), 
    (102, 15, 100, 'Child42292001'), 
    (103, 67, NULL, '20010119229'), 
    (104, 68, 103, 'Child7474272')

How to add dynamic field for each child item?

0 Answers0