declare @Table1 table (ID int, data varchar(500));
insert into @Table1
select 1, 'data1,data2,data3' UNION ALL
select 1, 'data2,data4' UNION ALL
select 2, 'data2,data3' UNION ALL
select 3, 'data1,data4,data3';
select * from @Table1
;WITH CTE AS
(
-- Assign a sequential row number to each ID, restart with each change in ID
SELECT t1.ID, t1.data, RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY data)
FROM @Table1 t1
),
CTE2 AS
(
-- assign a sequential row number to each ID/item, restart with each change in ID/item
SELECT t1.ID, ds.Items, RN, RN2 = ROW_NUMBER() OVER (PARTITION BY t1.ID, ds.ITEMs ORDER BY t1.ID, t1.RN)
FROM CTE t1
CROSS APPLY dbo.SplitString(t1.data, ',') ds
)
-- get the results, only getting the first item for each id.
SELECT ID, Items FROM CTE2 WHERE RN2 = 1
ORDER BY ID, RN
