Divide one column into multiple columns, but the data will be different from SQL

Question

I have a rowset in my database that looks like this:

Mr Joe Bloggs Joe Bloggs Joe Jim Bloggs Joe Jim David Bloggs Mr Joe Jim Bloggs Mr Joe Jim David Bloggs 

Required Conclusion:

 Title | First Name | Middle Name | Surname ------------------------------------------ Mr | Joe | x | Bloggs x | Joe | x | Bloggs x | Joe | Jim | Bloggs x | Joe | Jim David | Bloggs Mr | Joe | Jim | Bloggs Mr | Joe | Jim David | Bloggs 

This will be executed in the SQL stored procedure. I need this output, created by cycling through CURSOR and 4 variables to update 4 columns in the database. But, as you can see, the data is in 1 column and varies.

I find it difficult to understand the best way to do this and you will like your help. Let me know if you need anything else.

Similar questions are for mine, but I feel that they do not give me the complete answer that I need, or I think that it does not answer my question:

• Split a column row into multiple column rows
• split selection results (single column) into multiple columns
• Separate SQL column divided into multiple columns
• How can I split a column value into different columns in SQL .
• Sql query to split one column into two columns
• Is a column of a table divided into two columns in sql?

EDIT:

  --Temporary Table to Merge the full name together in order to split by comma. Each split will have a unique record but with the same address information for each name. CREATE TABLE #TempOutput ( ClientReference varchar(MAX), Prefix varchar(MAX), Reference int, MergedFullName varchar(MAX), FormatName varchar(MAX), Title varchar(MAX), FirstName varchar(MAX), MiddleName varchar(MAX), Surname varchar(MAX) ) DECLARE @Ref varchar(MAX) DECLARE @Prefix varchar(MAX) DECLARE @Reference int DECLARE @MergedName varchar(MAX) DECLARE @FormatName varchar(MAX) DECLARE @Title varchar(MAX) DECLARE @FirstName varchar(MAX) DECLARE @MiddleName varchar(MAX) DECLARE @Surname varchar(MAX) DECLARE ExtraNameSplitCursor CURSOR FOR SELECT [Client Reference], Prefix, Reference, CASE WHEN LEFT(Surname,3) = 'Mr,' THEN (Title + ' ' + [First Name] + ' ' + [Middle Name] + ' ' + Replace(Surname,'Mr,','')) WHEN LEFT(Surname,4) = 'Mrs,' THEN (Title + ' ' + [First Name] + ' ' + [Middle Name] + ' ' + Replace(Surname,'Mrs,','')) ELSE (Title + ' ' + [First Name] + ' ' + [Middle Name] + ' ' + Surname) END AS [Merged Full Name] FROM #StartResults OPEN ExtraNameSplitCursor FETCH NEXT FROM ExtraNameSplitCursor INTO @Ref, @Prefix, @Reference, @MergedName WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #TempOutput (ClientReference, Prefix, Reference, MergedFullName) SELECT @Ref, @Prefix, @Reference, GUID, FROM dbo.fn_SPLIT_LIST_STRING (@MergedName) --SELECT CASE -- WHEN @FormatName.GUID = 'Mr ' THEN @Title = 'Mr' -- WHEN LEFT(@MergedName,4) = 'Mrs ' THEN @Title = 'Mrs' -- WHEN LEFT(@MergedName,5) = 'Miss ' THEN @Title = 'Miss' -- WHEN LEFT(@MergedName,3) = 'Ms ' THEN @Title = 'Ms' -- WHEN LEFT(@MergedName,3) = 'Dr ' THEN @Title = 'Dr' -- WHEN LEFT(@MergedName,4) = 'Rev ' THEN @Title = 'Rev' -- WHEN LEFT(@MergedName,5) = 'Lord ' THEN @Title = 'Lord' -- ELSE @Title = '' --END AS Title, --CASE -- WHEN MID(@MergedName,2,1) = ' ' THEN @FirstName = '' ELSE @FirstName = CHARINDEX(' ', MergedFullName + ' ') FROM #TempOutput AS FirstName FETCH NEXT FROM ExtraNameSplitCursor INTO @Ref, @Prefix, @Reference, @MergedName, @FlatNo, @HouseName, @HouseNo, @Street, @District, @PostTown, @County, @PostCode END CLOSE ExtraNameSplitCursor DEALLOCATE ExtraNameSplitCursor --Select statement for temporary table SELECT ISNULL(ClientReference,'') AS ClientReference, ISNULL(Prefix,'') AS Prefix, ISNULL(Reference,'') AS Reference, ISNULL(MergedFullName,'') AS MergedFullName, ISNULL(Title,'') AS Title, ISNULL(FirstName,'') AS FirstName, ISNULL(MiddleName,'') AS MiddleName, ISNULL(Surname,'') AS Surname FROM #TempOutput Result ORDER BY ClientReference for xml auto --Drop all temporary tables DROP TABLE #TempOutput DROP TABLE #StartResults END 

Answer 1

I solved my problem and thanks for the tips and help.

I basically added a second cursor that does the following:

• Grab the Combined Full Name field and put it in a temporary variable.
• Checks the header by running the instruction to check Mr, Mrs, etc. otherwise enter an empty string in Title.
• Update name Crop the name of the combined full name
• Check the surname by RIGHT (@TempName, CHARINDEX ('', REVERSE (@TempName)) - 1) and suppose that it is a surname.
• Update Trim surname and replace it with the combined full name
• To the left of the name to the left of LEFT (@TempName, CHARINDEX ('',
  @TempName + '') -1)
• Update name for name and replace with its associated full name
• Everything else is placed in the middle name.

The bit is long, but in 99.9% of cases in my database it is formally processed.

If you want the code above let me know.

Thank you for your time.