How to add dynamic field for each child item?

Transact-SQL
1

Hi. 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 :slightly_smiling:

2

This might do it:

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')

SELECT k.kit_id, k.NAME, stuff(ca.kitmap, 1, 3, '') AS kitmap
FROM @kits k
CROSS APPLY (
    SELECT ' | ' + k2.NAME + ' | ' + km.barcode
    FROM @Kits k1
    JOIN @Kits k2 ON k1.kit_id = k2.kit_parent_id
    JOIN @KitMap km ON km.kit_id = k1.kit_id
    WHERE k1.kit_id = k.kit_id
    FOR XML path('')
    ) ca(kitmap)
WHERE ca.kitmap IS NOT NULL