create table splittedtable (
	column1 int,
	column2 varchar(5),
	column3 varchar(5),
	column4 date
)
GO;

declare @a varchar(max) = '1,a,b,20080101|2,c,d,20080102|3,e,f,20080102';

with cte as (
	select
		s.id, 
		column1 = case when t.id = 1 then t.val else null end,
		column2 = case when t.id = 2 then t.val else null end,
		column3 = case when t.id = 3 then t.val else null end,
		column4 = case when t.id = 4 then t.val else null end
	from dbo.SPLIT(@a,'|') s
	cross apply dbo.SPLIT(s.val,',') t
)
insert into splittedtable
select
	column1 = MAX(column1),
	column2 = MAX(column2),
	column3 = MAX(column3),
	column4 = MAX(column4)
from cte
group by id

select * from splittedtable

USE [tempdb]
GO

IF OBJECT_ID('dbo.fn_split') IS NOT NULL
DROP FUNCTION dbo.fn_split;
GO
CREATE FUNCTION dbo.fn_split(@arr AS NVARCHAR(MAX), @sep AS NCHAR(1))
RETURNS TABLE
AS
RETURN
WITH 
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
  ,Numbers AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  
SELECT
(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,
LTRIM(SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n)) AS element
FROM Numbers
WHERE n <= LEN(@arr) + 1
AND SUBSTRING(@sep + @arr, n, 1) = @sep
AND Numbers.n <= 1000 -- make sure to change to an appropriate value
GO

DECLARE @var VARCHAR(MAX)
SET @var ='1,a,b,20080101|2,c,d,20080102|3,e,f,20080102'

SELECT 
	PARSENAME(REPLACE(element,',','.'),4) AS Col1,
	PARSENAME(REPLACE(element,',','.'),3) AS Col2,
	PARSENAME(REPLACE(element,',','.'),2) AS Col3,
	PARSENAME(REPLACE(element,',','.'),1) AS Col4
FROM dbo.fn_split(@var,'|')