title: SQL Server - PIVOT - two columns into rows
body: <p>I saw many questions about PIVOT for a single column, each question more complex than other, however, I could not find anything like what I need.</p>
  
  <p>To be honest, I don't even know if pivot will help me in this situation.</p>
  
  <p>Let's say I have this data on my source table:</p>
  
  <pre><code>SELECT '1' as 'RowId', 'RandomName1' as 'First', 'RandomLast1' as 'Last'
  UNION
  SELECT '2' as 'RowId', 'RandomName2' as 'First', 'RandomLast2' as 'Last'
  UNION
  SELECT '3' as 'RowId', 'RandomName3' as 'First', 'RandomLast3' as 'Last'
  UNION
  SELECT '4' as 'RowId', 'RandomName4' as 'First', 'RandomLast4' as 'Last'
  UNION
  SELECT '5' as 'RowId', 'RandomName5' as 'First', 'RandomLast5' as 'Last'
  </code></pre>
  
  <p>Maximum of 5 rows with the first name and last name.
  The value of the columns First and Last will be random.</p>
  
  <pre><code>RowId First       Last
  ----- ----------- -----------
  1     RandomName1 RandomLast1
  2     RandomName2 RandomLast2
  3     RandomName3 RandomLast3
  4     RandomName4 RandomLast4
  5     RandomName5 RandomLast5
  </code></pre>
  
  <p>I was trying to pivot this data to something like this:</p>
  
  <pre><code>First1      Last1       First2      Last2       First3      Last3       First4      Last4       First5      Last5
  ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
  RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 RandomName5 RandomLast5
  </code></pre>
  
  <p>For example:
  I don't have any problem if columns First5 and Last5 are NULL because there are only 4 rows.</p>
  
  <pre><code>First1      Last1       First2      Last2       First3      Last3       First4      Last4       First5      Last5
  ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
  RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 NULL        NULL
  </code></pre>
  
  <p>Can anyone give me a little help? 
  Thanks.</p>
  
  <hr>
  
  <p>Solution based on Sheela K R answers:</p>
  
  <pre><code>SELECT 
      MAX(First1) as 'First1',  MAX(Last1) as 'Last1',
      MAX(First2) as 'First2',  MAX(Last2) as 'Last2',
      MAX(First3) as 'First3',  MAX(Last3) as 'Last3',
      MAX(First4) as 'First4',  MAX(Last4) as 'Last4',
      MAX(First5) as 'First5',  MAX(Last5) as 'Last5'
  FROM
  (
      SELECT 
          CASE WHEN RowId = 1 THEN [First] END as 'First1',
          CASE WHEN RowId = 1 THEN [Last] END as 'Last1',
          CASE WHEN RowId = 2 THEN [First] END as 'First2',
          CASE WHEN RowId = 2 THEN [Last] END as 'Last2',
          CASE WHEN RowId = 3 THEN [First] END as 'First3',
          CASE WHEN RowId = 3 THEN [Last] END as 'Last3',
          CASE WHEN RowId = 4 THEN [First] END as 'First4',
          CASE WHEN RowId = 4 THEN [Last] END as 'Last4',
          CASE WHEN RowId = 5 THEN [First] END as 'First5',
          CASE WHEN RowId = 5 THEN [Last] END as 'Last5'
      FROM
      (
          SELECT '1' as 'RowId', 'RandomName1' as 'First', 'RandomLast1' as 'Last'
          UNION SELECT '2' as 'RowId', 'RandomName2' as 'First', 'RandomLast2' as 'Last'
          UNION SELECT '3' as 'RowId', 'RandomName3' as 'First', 'RandomLast3' as 'Last'
          UNION SELECT '4' as 'RowId', 'RandomName4' as 'First', 'RandomLast4' as 'Last'
          --UNION SELECT '5' as 'RowId', 'RandomName5' as 'First', 'RandomLast5' as 'Last'
      ) test
  ) test2
  </code></pre>