title: MS SQL Temporary table looping
body: <p>Need help on sql looping query</p>
  
  <p><strong>my table :</strong></p>
  
  <p><a href="https://i.stack.imgur.com/pRvI4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pRvI4.png" alt="enter image description here"></a></p>
  
  <p><strong>my expecting output:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/Rqbec.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Rqbec.png" alt="enter image description here"></a></p>
  
  <p>I am able to get 1 line only..</p>
  
  <pre><code> declare @ID nvarchar(50) = (select EMP_ID from HRS_WORKFLOW01);
  
  
      CREATE TABLE #TEMP(EMP_ID NVARCHAR(200),EMP_L1 NVARCHAR(200),EMP_L2 NVARCHAR(200),EMP_L3 NVARCHAR(200),EMP_L4 NVARCHAR(200))
      DECLARE @L1 NVARCHAR(50);
      DECLARE @L2 NVARCHAR(50);
      DECLARE @L3 NVARCHAR(50);
      DECLARE @L4 NVARCHAR(50);
  
      SET @L1 = (SELECT L1EmplNo FROM HRS_WORKFLOW01 WHERE EMP_ID =@ID)
      INSERT INTO #TEMP (EMP_ID,EMP_L1)VALUES(@ID,@L1)
  
      SET @L2 = (SELECT L1EmplNo FROM HRS_WORKFLOW01 WHERE EMP_ID =@L1)
      UPDATE #TEMP SET EMP_L2=@L2 WHERE EMP_ID=@ID
  
      SET @L3 = (SELECT L1EmplNo FROM HRS_WORKFLOW01 WHERE EMP_ID =@L2)
      UPDATE #TEMP SET EMP_L3=@L3 WHERE EMP_ID=@ID
  
      SET @L4 = (SELECT L1EmplNo FROM HRS_WORKFLOW01 WHERE EMP_ID =@L3)
      UPDATE #TEMP SET EMP_L4=@L4 WHERE EMP_ID=@ID
  
      SELECT * FROM #TEMP
  </code></pre>