How can I get around differences in column types when using unpivot?

Question

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. Consider this example: ``` 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) ) ``` The result would be this: ``` PersonId ColumnName Value ----------- ----------------- ---------------- 1 Abe Firstname 1 Albertson Lastname 2 Benny Firstname 2 Boomboom Lastname ``` Everything is unicorns and rainbows. Now I change the datatype of Lastname to varchar(25) and everything breaks. The output is: The type of column "Lastname" conflicts with the type of other columns specified in the UNPIVOT list. How can I get around this and convert…

Accepted Answer

You cannot convert inside the UNPIVOT syntax but you can convert the data inside a subquery similar to the following:

```
select PersonId, ColumnName, Value  
from
(
  select personid, 
    firstname, 
    cast(lastname as varchar(50)) lastname
  from People
) d
unpivot
(
  Value FOR 
  ColumnName in (FirstName, LastName)
) unpiv;
```

See SQL Fiddle with Demo

Another way to do this would be to use CROSS APPLY, depending on your version of SQL Server you can use CROSS APPLY with VALUES or UNION ALL:

```
select PersonId, ColumnName, Value  
from People
cross apply
(
  select 'firstname', firstname union all
  select 'lastname', cast(lastname as varchar(50))
) c (ColumnName, value)
```

See SQL Fiddle with Demo.

How can I get around differences in column types when using unpivot?

Solution

Problem