Use DelimitedSplit8K[/url]

something like this:

SELECT test.Manager, test.Device, split.ItemNumber, Item = RTRIM(split.Item)

FROM #tbl_data test

CROSS APPLY dbo.DelimitedSplit8k(test.Manager,',') split;