Unpivot columns using CROSS APPLY where values include int and varchar

Question

I am attempting to unpivot a table column but am encountering a conversion error from varchar to int. All of the columns in my table are ints except one, which is varchar. The error is detailed below.

I don't want to use any type of conversion.

Conversion failed when converting the varchar value 'View Test' to data type int.

DECLARE @tableData AS TABLE 
(
    TD_ID INT PRIMARY KEY IDENTITY(1,1),
    View0 INT, 
    View1 INT,
    View2 INT,
    Remarks Varchar(20)
)

INSERT INTO @tableData (View0, View1, View2, Remarks)
VALUES(1, 0, 1, 'View Test')

SELECT column_Name, column_value
FROM @tableData
CROSS APPLY (
    VALUES
    ('View0',View0)
    , ('View1',View1)
    , ('View2',View2)
    , ('Remarks',Remarks)
) Q (column_Name, column_value)

Answer 1

Like @Dale K said, you can convert to varchar first. Here is the sample code:

DECLARE @tableData AS TABLE (
    TD_ID INT PRIMARY KEY IDENTITY(1,1),
    View0 INT,
    View1 INT,
    View2 INT,
    Remarks Varchar(20)
)

INSERT INTO @tableData (View0, View1, View2, Remarks)
VALUES (1, 0, 1, 'View Test')

SELECT column_Name, column_value
FROM (
    SELECT
        CAST(View0 AS VARCHAR(20)) AS View0,
        CAST(View1 AS VARCHAR(20)) AS View1,
        CAST(View2 AS VARCHAR(20)) AS View2,
        Remarks
    FROM @tableData
) src
UNPIVOT (
    column_value FOR column_Name IN (View0, View1, View2, Remarks)
) unpvt;

Here is the fiddle link

Or you can use like this too:

SELECT column_Name, column_value
FROM @tableData
CROSS APPLY (
    VALUES
    ('View0', CAST(View0 AS VARCHAR(20))),
    ('View1', CAST(View1 AS VARCHAR(20))),
    ('View2', CAST(View2 AS VARCHAR(20))),
    ('Remarks', Remarks)
) Q (column_Name, column_value)

Here is the fiddle link

Answer 2

If you want to scrimp on conversion but waste some typing you can use OPENJSON as a middle-man to do it for you:

DECLARE @tableData AS TABLE 
(
    TD_ID INT PRIMARY KEY IDENTITY(1,1),
    View0 INT, 
    View1 INT,
    View2 INT,
    Remarks Varchar(20)
)

INSERT INTO @tableData (View0, View1, View2, Remarks)
VALUES(1, 0, 1, 'View Test')

SELECT oj.[key], oj.value, o.[key]
FROM OPENJSON((SELECT *
FROM @tableData
FOR JSON PATH)) o
CROSS APPLY OPENJSON(o.value) oj
WHERE oj.[key] <> 'TD_ID'

Openjson can return values in form of "key" / "value", which we do here, by:

1. Converting your table data into json.
2. Re-opening it by OPENJSON, which returns an list of rows of json objects (the o alias part)
3. Opening each json object to return values in key/value form (the oj alias part)

You could skip the 3rd part in your example and do OPENJSON(..., '$[0]') o to directly get the values, but this will only work for single row tables.

I would say that while this is a possible approach, the best way is still to do your casting properly