title: Split one column to multiple columns but data will vary SQL
body: <p>I have a set of row in my database that look like this:</p>
  
  <pre><code>Mr Joe Bloggs
  Joe Bloggs
  Joe Jim Bloggs
  Joe Jim David Bloggs
  Mr Joe Jim Bloggs
  Mr Joe Jim David Bloggs
  </code></pre>
  
  <p>Output required:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This will be run in a SQL Stored Procedure. What i need is this output generated while looping through a CURSOR and the 4 variables to update the 4 columns in the database. But as you can see the data is in 1 column and varies.</p>
  
  <p>I am finding it hard to understand the best way of doing this and would like your help please. Let me know if you need anything else.</p>
  
  <p>Similar Questions to mine but i feel these dont give me the full answer i need or i think it doesnt answer my question:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/6823924/split-column-string-into-multiple-columns-strings">Split column string into multiple columns strings</a></li>
  <li><a href="https://stackoverflow.com/questions/9980695/split-select-results-one-column-into-multiple-columns">split select results (one column) into multiple columns</a></li>
  <li><a href="https://stackoverflow.com/questions/11391348/sql-single-column-split-over-mutiple-columns">SQL single column split over mutiple columns</a></li>
  <li><a href="https://stackoverflow.com/questions/11408033/how-can-i-split-column-value-into-different-columns-in-sql">How can i split column value into different columns in SQL</a></li>
  <li><a href="https://stackoverflow.com/questions/12435469/sql-query-to-split-one-column-into-two-columns">Sql query to split one column into two columns</a></li>
  <li><a href="https://stackoverflow.com/questions/13507592/table-column-split-to-two-columns-in-sql">Table column split to two columns in sql?</a></li>
  </ul>
  
  <p>EDIT:</p>
  
  <pre><code>    --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
  </code></pre>