This should work:
CREATE FUNCTION [dbo].[split](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','nvarchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
GO
declare @csv as varchar(max) =
'Agentid,agentname, phone, email, address,state,country, zip,ssn
1,nick,56677700987,abc@yahoo.com,address,tx,USA,12345,9990009999
2,Jack,12677700987,abcuu@yahoo.com,address,tx,USA,145,12990009999
3,mike,56677700987,abcd@yahoo.com,address,tx,USA,12345,9990009999'
SELECT
Row_Num,
[1] as [Agentid],
[2] as [agentname],
[3] as [phone],
[4] as [email],
[5] as [address],
[6] as [state],
[7] as [country],
[8] as [zip],
[9] as [ssn]
FROM (
SELECT
r.id as [Row_Num],
c.id as [ColNum],
LTRIM(RTRIM(c.val)) as [ColValue]
FROM dbo.split(@csv,CHAR(13)+CHAR(10)) as r
CROSS APPLY dbo.split(r.[val],',') as c
WHERE r.id <> 1
) as src
PIVOT
(
MAX(ColValue) FOR ColNum IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
) as pvt
You cannot use the string_split() function because it does not return the position of the fields.