As pointed out in the comments, SQL is a bit difficult language for dynamic amount of columns. Having that said, you could split the string into multiple rows and then select individual names.
One way to do this is to create a custom function. Consider this example
CREATE FUNCTION CustomStringSplit(@value nvarchar(max), @delimiter nvarchar(max))
RETURNS @items TABLE (
Ordinal int,
Part nvarchar(max)
) AS
BEGIN
WITH Items (Ordinal, Part, Remainder) AS (
SELECT 1 AS Ordinal,
CASE
WHEN CHARINDEX(@delimiter, @value) > 0 THEN LEFT(@value, CHARINDEX(@delimiter, @value) - 1)
ELSE @value
END AS Part,
CASE
WHEN CHARINDEX(@delimiter, @value) > 0 THEN SUBSTRING(@value, CHARINDEX(@delimiter, @value) + 1, 99999999)
ELSE NULL
END AS Remainder
UNION ALL
SELECT Items.Ordinal + 1 AS Ordinal,
CASE
WHEN CHARINDEX(@delimiter, Remainder) > 0 THEN LEFT(Remainder, CHARINDEX(@delimiter, Remainder) - 1)
ELSE Remainder
END AS Part,
CASE
WHEN CHARINDEX(@delimiter, Remainder) > 0 THEN SUBSTRING(Remainder, CHARINDEX(@delimiter, Remainder) + 1, 99999999)
ELSE NULL
END AS Remainder
FROM Items
WHERE Items.Remainder IS NOT NULL
)
INSERT INTO @items (Ordinal, Part)
SELECT Items.Ordinal, Items.Part FROM Items;
RETURN;
END;
After creating the function above, you can try to use it with
DECLARE @name varchar(100);
SET @name = 'ahmed ali adham gaber sumer ahmed';
SELECT * FROM CustomStringSplit(@name, ' ');
The result is
Ordinal Part
------- -----
1 ahmed
2 ali
3 adham
4 gaber
5 sumer
6 ahmed
In order to get the data as columns you could try for example
DECLARE @name varchar(100);
SET @name = 'ahmed ali adham gaber sumer ahmed';
SELECT @name,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 1) AS Col1,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 2) AS Col2,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 3) AS Col3,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 4) AS Col4,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 5) AS Col5,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 6) AS Col6,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 7) AS Col7,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 8) AS Col8,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 9) AS Col9,
(SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 10) AS Col10
The result should be
(No column name) Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10
--------------------------------------------------------------------
ahmed ali adham gaber sumer ahmed ahmed ali adham gaber sumer ahmed NULL NULL NULL NULL
If you're using SQL Server 2016, you could also try using
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[
^] instead of the custom function.