Here's another way to do the same thing but using a single CROSS APPLY of DelimitedSplit8K.

WITH SampleData ([Identity], Name, Col1, Col2, Col3) AS

(

SELECT 1,'Test1','1,2,3','200,300,400','3,4,6'

UNION ALL SELECT 2,'Test2','3,4,5','300,455,600','2,3,8'

UNION ALL SELECT 3,'Test3','30,40,50,60','3000,4550,6000,7000','20,30,80,90'

)

SELECT [Identity], Name--, Item, ItemNumber, rn

,Col1=MAX(CASE (ItemNumber+rn-1)/rn WHEN 1 THEN Item END)

,Col2=MAX(CASE (ItemNumber+rn-1)/rn WHEN 2 THEN Item END)

,Col3=MAX(CASE (ItemNumber+rn-1)/rn WHEN 3 THEN Item END)

FROM

(

SELECT [Identity], Name, Item, ItemNumber

,rn=MAX(ItemNumber) OVER (PARTITION BY [Identity], Name)/3

FROM SampleData a

CROSS APPLY (SELECT Col1+','+Col2+','+Col3) b(Col)

CROSS APPLY dbo.DelimitedSplit8K(Col, ',') c

) a

GROUP BY [Identity], Name, (ItemNumber)%rn

ORDER BY [Identity], Name, Col1;

I do wonder what would happen to Luis's and my solutions if one of the lists in any of the columns was short a value. 😛 Not sure if that's a case you need to consider.

Well actually, I do know what would happen in my case (the results would get garbled in different ways depending on which column was missing the item). Didn't test Luis's case although I suspect it would simply be missing some rows.