UNPIVOT Return Data Types

Question

I am using the UNPIVOT operator and am getting an unwanted data type returned. The query is returning an nvarchar data type for the unpivoted data. When joining to a table with varchar data type I get and an implicit type conversion. Below is an example that will produce these results.

Is there a way to control the output data types returned by UNPIVOT? I would like to have UNPIVOT return the varchar data type.

CREATE TABLE #Data
(
    Facility varchar(10) NOT NULL,
    Dt datetime NOT NULL,
    VolumeType1 int NOT NULL,
    VolumeType2 int NOT NULL,
    VolumeType3 int NOT NULL
);

CREATE TABLE #Map
(
    Facility varchar(10) NOT NULL,
    Department varchar(15) NOT NULL,
    VolumeType varchar(15) NOT NULL
)

INSERT INTO #Data VALUES
('Building1', '08/01/2016', 2500, 2500, 30),
('Building1', '08/02/2016', 3000, 3000, 35),
('Building2', '08/01/2016', 2500, 2500, 30),
('Building2', '08/02/2016', 3000, 3000, 35)

INSERT INTO #Map VALUES
('Building1', 'Department1', 'VolumeType1'),
('Building1', 'Department2', 'VolumeType2'),
('Building2', 'Department3', 'VolumeType1')

SELECT
    *
FROM 
    #Data
    UNPIVOT
        (
            Volume FOR VolumeType IN (VolumeType1, VolumeType2, VolumeType3) 
        ) AS UNP
    JOIN #Map
        ON UNP.Facility = #Map.Facility
            AND UNP.VolumeType = #Map.VolumeType

Answer 1

The UNPIVOT function is a bit of an odd function in that it will take data from various columns (with the same datatype and length) and convert it into rows.

While it converts the data it does seem to make a few assumptions along the way. For example, your Volume column gets the datatype of int because that was the datatype of each of the columns you unpivoted, but the problem is with your VolumeType column - the engine doesn't know if you have a varchar or a nvarchar for data, so when you perform the unpivot it gives the nvarchar(256).

If you don't want the implicit type conversion, then I'd suggest using CROSS APPLY to unpivot your data. It's a few more lines of code, but you can include the explicit data type if needed. Here is an example:

select *
from
(
    select Facility, Dt, VolumeType, Volume
    from #Data
    cross apply
    (
        select 'VolumeType1', VolumeType1 union all
        select 'VolumeType2', VolumeType2 union all
        select 'VolumeType3', VolumeType3
    ) c (VolumeType, Volume)
) UNP
JOIN #Map
        ON UNP.Facility = #Map.Facility
            AND UNP.VolumeType = #Map.VolumeType;

See Demo. This should remove the implicit conversion, but if it doesn't, then you can perform the conversion inside of the CROSS APPLY:

select *
from
(
    select Facility, Dt, VolumeType, Volume
    from #Data
    cross apply
    (
        select cast('VolumeType1' as varchar(15)), VolumeType1 union all
        select cast('VolumeType2' as varchar(15)), VolumeType2 union all
        select cast('VolumeType3' as varchar(15)), VolumeType3
    ) c (VolumeType, Volume)
) UNP
JOIN #Map
        ON UNP.Facility = #Map.Facility
            AND UNP.VolumeType = #Map.VolumeType;     

See Demo.

Your question is very similar to one I posted over on DBA.SE about why UNPIVOT requires all the datatype lengths to be the same.