title: How can I get around differences in column types when using unpivot?
body: <p>I am having problems using unpivot on columns, that are not the exact same datatype, and I can't figure out how to convert the columns on the fly, because the syntax for UNPIVOT does not seem to support it.</p>
  
  <p>Consider this example:</p>
  
  <pre><code>DECLARE @People TABLE
  (PersonId int, Firstname varchar(50), Lastname varchar(50))
  -- Load Sample Data
  INSERT INTO @People VALUES (1, 'Abe', 'Albertson')
  INSERT INTO @People VALUES (2, 'Benny', 'Boomboom')
  
  SELECT PersonId, ColumnName, Value FROM @People
  UNPIVOT
  (
    ColumnName FOR 
    Value IN (FirstName, LastName)
  ) 
  </code></pre>
  
  <p>The result would be this:</p>
  
  <pre><code>PersonId    ColumnName        Value
  ----------- ----------------- ----------------
  1           Abe               Firstname
  1           Albertson         Lastname
  2           Benny             Firstname
  2           Boomboom          Lastname
  </code></pre>
  
  <p>Everything is unicorns and rainbows. Now I change the datatype of Lastname to varchar(25) and everything breaks. The output is:</p>
  
  <blockquote>
    <p>The type of column "Lastname" conflicts with the type of other columns
    specified in the UNPIVOT list.</p>
  </blockquote>
  
  <p>How can I get around this and convert everything to say a varchar(50) on the fly, without tampering with the actual data types on the table?</p>
  
  <p>SqlFiddle working example (same datatype): <a href="http://sqlfiddle.com/#!3/f3719" rel="noreferrer">http://sqlfiddle.com/#!3/f3719</a></p>
  
  <p>SqlFiddle broken example (diff datatypes): <a href="http://sqlfiddle.com/#!3/5dca13/1" rel="noreferrer">http://sqlfiddle.com/#!3/5dca13/1</a></p>