Most succinct way to transform a CSV string to a table in T-SQL?

What i do in this case is just using some string replace to convert it to json and open the json like a table. May not be suitable for every use case but it is very simple to get running and works with strings and files. With files you just need to watch your line break character, mostly i find it to be "Char(13)+Char(10)"

declare @myCSV nvarchar(MAX)= N'"Id";"Duration";"PosX";"PosY"
"•P001";223;-30;35
"•P002";248;-28;35
"•P003";235;-26;35'

--CSV to JSON
    --convert to json by replacing some stuff
    declare @myJson nvarchar(MAX)= '[['+  replace(@myCSV, Char(13)+Char(10), '],[' )  +']]'
        set @myJson = replace(@myJson, ';',',')         -- Optional: ensure coma delimiters for json if the current delimiter differs
    --  set @myJson = replace(@myJson, ',,',',null,')   -- Optional: empty in between
    --  set @myJson = replace(@myJson, ',]',',null]')   -- Optional: empty before linebreak
    
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))-1 AS LineNumber, *
    FROM   OPENJSON( @myJson ) 
    with (
         col0   varchar(255)    '$[0]'
        ,col1   varchar(255)    '$[1]'
        ,col2   varchar(255)    '$[2]'
        ,col3   varchar(255)    '$[3]'
        ,col4   varchar(255)    '$[4]'
        ,col5   varchar(255)    '$[5]'
        ,col6   varchar(255)    '$[6]'
        ,col7   varchar(255)    '$[7]'
        ,col8   varchar(255)    '$[8]'  
        ,col9   varchar(255)    '$[9]'
        --any name column count is possible
    ) csv
    order by (SELECT 0) OFFSET 1 ROWS --hide header row

Using SQL Server's built in XML parsing is also an option. Of course, this glosses over all the nuances of an RFC-4180 compliant CSV.

-- Given a CSV string like this:
declare @roles varchar(800)
select  @roles = 'Pub,RegUser,ServiceAdmin'

-- Here's the XML way
select split.csv.value('.', 'varchar(100)') as value
from (
     select cast('<x>' + replace(@roles, ',', '</x><x>') + '</x>' as xml) as data
) as csv
cross apply data.nodes('/x') as split(csv)

If you are using SQL 2016+, using string_split is better, but this is a common way to do this prior to SQL 2016.

Even the accepted answer is working fine. but I got this function much faster even for thousands of record. create below function and use.

        IF EXISTS (
            SELECT 1
            FROM Information_schema.Routines
            WHERE Specific_schema = 'dbo'
                AND specific_name = 'FN_CSVToStringListTable'
                AND Routine_Type = 'FUNCTION'
            )
    BEGIN
        DROP FUNCTION [dbo].[FN_CSVToStringListTable]
    END
    GO
    
    CREATE FUNCTION [dbo].[FN_CSVToStringListTable] (@InStr VARCHAR(MAX))
    RETURNS @TempTab TABLE (Id NVARCHAR(max) NOT NULL)
    AS
    BEGIN
            ;-- Ensure input ends with comma
    
        SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    
        DECLARE @SP INT
        DECLARE @VALUE VARCHAR(1000)
    
        WHILE PATINDEX('%,%', @INSTR) <> 0
        BEGIN
            SELECT @SP = PATINDEX('%,%', @INSTR)
    
            SELECT @VALUE = LEFT(@INSTR, @SP - 1)
    
            SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
    
            INSERT INTO @TempTab (Id)
            VALUES (@VALUE)
        END
    
        RETURN
    END
    GO

---Test like this.

    declare @v as NVARCHAR(max) = N'asdf,,as34df,234df,fs,,34v,5fghwer,56gfg,';
    SELECT Id FROM dbo.FN_CSVToStringListTable(@v)

I was about you use the solution mentioned in the accepted answer, but doing more research led me to use Table Value Types:

These are far more efficient and you don't need a TVF (Table valued function) just to create a table from csv. You can use it directly in your scripts or pass that to a stored procedure as a Table Value Parameter. The Type can be created as :

CREATE TYPE [UniqueIdentifiers] AS TABLE(
    [Id] [varchar](20) NOT NULL
)