title: How to convert vertical data to horizontal dynamically without using Cursor or Pivot
body: <p>Currently I am trying to convert vertical data structure to horizontal data dynamically by implementing a cursor in the stored procedure. However, the execution time takes too long. I am trying to find a way to optimise this approach. What is the better way to use other than Cursor? Note: PIVOT cant be used because of scattered data mapping. This is my current code with sample data and expected output:</p>
  <p>What I have done so far: There are three temporary tables #tablestructure, #raw and #dsid. #Tablestruture contains all the columns under destfieldname which are needed to create a table. Then once it is created, we need to take the contents from #raw and insert it into the new table which has been created based on the row and column number</p>
  <p>#tablestructure</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>desttablename</th>
  <th>destfieldname</th>
  <th>datatype</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>*</td>
  <td>subjectnumber</td>
  <td>int</td>
  </tr>
  <tr>
  <td>sample</td>
  <td>personame</td>
  <td>nvarchar(20)</td>
  </tr>
  <tr>
  <td>sample</td>
  <td>personlocation</td>
  <td>nvarchar(20)</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>#raw table</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>rownumber</th>
  <th>columnnumber</th>
  <th>fieldname</th>
  <th>contents</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>subjectnumber</td>
  <td>132516352</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>personname</td>
  <td>Alex</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>subjectnumber</td>
  <td>132516353</td>
  </tr>
  <tr>
  <td>2</td>
  <td>3</td>
  <td>personlocation</td>
  <td>Canada</td>
  </tr>
  <tr>
  <td>1</td>
  <td>3</td>
  <td>personlocation</td>
  <td>Australia</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>personname</td>
  <td>John</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>#DSID Table</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>projectid</th>
  <th>respondentfieldname</th>
  <th>debug</th>
  <th>fileincludedheaders</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>123</td>
  <td>subjectnumber</td>
  <td>NULL</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Expected output</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Subjectnumber</th>
  <th>PersonName</th>
  <th>Personlocation</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>132516352</td>
  <td>Alex</td>
  <td>Australia</td>
  </tr>
  <tr>
  <td>132516353</td>
  <td>John</td>
  <td>Canada</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>declare createSQL cursor for 
  select distinct 'drop table TestTmp.dbo.' + t.destname  as droptableSQL,
           'CREATE TABLE TestTmp.dbo.' + t.destname    + ' (' + stuff((
              select ',[' + destfieldname + '] ' + f.datatype from #tableStructure f 
              where (f.desttablename in (t.desttablename, '*')) 
              group by destfieldname, f.datatype) 
      for xml path ('')),1,1,'') + ', [sequenceID] int, [subsequenceID] int)'  as createTableSQL,
  'insert into VectorNormalizerTmp.dbo.' + t.desttablename + @tablesuffix + '([' + replace(replace(respondentidfieldname,']',''),'[','') + '],sequenceid, subsequenceid) 
  SELECT a.contents, b.sequenceid, b.subsequenceid from (select contents from #raw where fieldname = ''' + respondentidfieldname + ''') a 
  CROSS JOIN (select sequenceid, subsequenceid from #rowUniques where desttablename = ''' + desttablename +''') b ' as appendEmptyRowsSQL,
  'create index idx' + t.desttablename + @tablesuffix + ' on VectorNormalizerTmp.dbo.' + desttablename + @tablesuffix + '([' + respondentidfieldname + '],sequenceid, subsequenceid)' as idxSQL
  
  from (select distinct desttablename from #tablestructure where desttablename = 'Screener') t
  cross join (select respondentidfieldname, debug from #dsid) d
  
  
  declare @sqltorun nvarchar(max);
  declare @sqltorun2 nvarchar(max);
  declare @sqltorun3 nvarchar(max);
  declare @sqltorun4 nvarchar(max);
  
  open createSQL
  fetch next from createSQL into @sqltorun, @sqltorun2, @sqltorun3, @sqltorun4
  while @@FETCH_STATUS = 0 
  begin
      begin try
      --print @sqltorun
      EXECUTE sp_executesql @sqltorun
      end try
      begin catch
      --print '!!ERROR: ' + ERROR_MESSAGE()   
      end catch
      print @sqltorun2
      EXECUTE sp_executesql @sqltorun2
      print @sqltorun3
      EXECUTE sp_executesql @sqltorun3
      --print @sqltorun4
      begin try
      EXECUTE sp_executesql @sqltorun4
      end try
      begin catch
      --print '!!ERROR: ' + ERROR_MESSAGE()   
      end catch
      fetch next from createSQL into @sqltorun, @sqltorun2, @sqltorun3, @sqltorun4
  end
  close createSQL
  deallocate createSQL
  </code></pre>