Using dynamic unpivot with columns with different types

Question

i have a table with around 100 columns named F1, F2, ... F100. I want to query the data row-wise, like this:

F1: someVal1
F2: someVal2
...

I am doing all this inside a SP, therefore, I am generating the sql dynamically. I have successfully generated the following sql:

select CAST(valname as nvarchar(max)), CAST(valvalue as nvarchar(max)) from tbl_name unpivot
(
    valvalue for valname in ([form_id], [F1],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20],[F21],[F22],[F23],[F24],[F25],[F26],[F27],[F28],[F29],[F30],[F31],[F32],[F33],[F34],[F35],[F36],[F37],[F38],[F39],[F40],[F41],[F42],[F43],[F44],[F45],[F46],[F47],[F48],[F49],[F50],[F51],[F52],[F53],[F54],[F55],[F56],[F57],[F58],[F59],[F60],[F61],[F62],[F63],[F64],[F65],[F66],[F67],[F68],[F69],[F70],[F71],[F72],[F73],[F74],[F75],[F76],[F77],[F78],[F79],[F80],[F81],[F82],[F83],[F84],[F85])
) u

But on executing this query, I get this exception:

The type of column "F3" conflicts with the type of other columns specified in the UNPIVOT list.

I guess this is because F3 is varchar(100) while form_id, F1 and F2 are varchar(50). According to my understanding, I shouldn't be getting this error because I am casting all the results to nvarchar(max) in the select statement.

This table has all kinds of columns like datetime, smallint and int. Also, all the columns of this table except one have SQL_Latin1_General_CP1_CI_AS collaltion

What is the fix for this error ?

Answer 1

this solution is you must use a subquery to let all columns be the same type to have the same length.

Try to CAST the values in subquery then unpivot instead of select

select valname, valvalue 
from (
    SELECT  
        CAST([form_id] as nvarchar(max)) form_id, 
        CAST([F1] as nvarchar(max)) F1,
        CAST([F2] as nvarchar(max)) F2,
        CAST([F3] as nvarchar(max)) F3,
        CAST([F4] as nvarchar(max)) F4,
        ....
    FROM tbl_name 
) t1 unpivot
(
    valvalue for valname in ([form_id], [F1],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20],[F21],[F22],[F23],[F24],[F25],[F26],[F27],[F28],[F29],[F30],[F31],[F32],[F33],[F34],[F35],[F36],[F37],[F38],[F39],[F40],[F41],[F42],[F43],[F44],[F45],[F46],[F47],[F48],[F49],[F50],[F51],[F52],[F53],[F54],[F55],[F56],[F57],[F58],[F59],[F60],[F61],[F62],[F63],[F64],[F65],[F66],[F67],[F68],[F69],[F70],[F71],[F72],[F73],[F74],[F75],[F76],[F77],[F78],[F79],[F80],[F81],[F82],[F83],[F84],[F85])
) u

In a simplest way I would use CROSS APPLY with VALUES to do unpivot

SELECT * 
    FROM People CROSS APPLY (VALUES 
        (CAST([form_id] as nvarchar(max))),
        (CAST([F1] as nvarchar(max))),
        (CAST([F2] as nvarchar(max))),
        (CAST([F3] as nvarchar(max))),
        (CAST([F4] as nvarchar(max))),
        ....
    ) v (valvalue)

---

Here is a sample about CROSS APPLY with VALUES to do unpivot

we can see there are many different types in the People table.

we can try to use cast to varchar(max), let columns be the same type.

CREATE TABLE People
(
  IntVal int, 
  StringVal varchar(50), 
  DateVal date
)

INSERT INTO People VALUES (1, 'Jim', '2017-01-01');
INSERT INTO People VALUES (2, 'Jane', '2017-01-02');
INSERT INTO People VALUES (3, 'Bob', '2017-01-03');

Query 1:

SELECT * 
FROM People CROSS APPLY (VALUES 
    (CAST(IntVal AS VARCHAR(MAX))),
    (CAST(StringVal AS VARCHAR(MAX))),
    (CAST(DateVal AS VARCHAR(MAX)))
) v (valvalue)

Results:

| IntVal | StringVal |    DateVal |   valvalue |
|--------|-----------|------------|------------|
|      1 |       Jim | 2017-01-01 |          1 |
|      1 |       Jim | 2017-01-01 |        Jim |
|      1 |       Jim | 2017-01-01 | 2017-01-01 |
|      2 |      Jane | 2017-01-02 |          2 |
|      2 |      Jane | 2017-01-02 |       Jane |
|      2 |      Jane | 2017-01-02 | 2017-01-02 |
|      3 |       Bob | 2017-01-03 |          3 |
|      3 |       Bob | 2017-01-03 |        Bob |
|      3 |       Bob | 2017-01-03 | 2017-01-03 |

Note

when you use unpivot need to make sure the unpivot columns date type are the same.

Answer 2

Many ways a cat can skin you, or vice-versa. Jokes apart, what D-Shih suggested is what you should start with and may get you home and dry.

In a majority of cases; Essentially the UNPIVOT operation is concatenating the data from multiple rows. Starting with a CAST operation is the best way forward as it makes the data types identical(preferably a string type like varchar or nvarchar), its also a good idea to go with the same length for all UNPIVOTED columns in addition to having the same type.

In other cases; If this still does not solve the problem, then you need to look deeper and check whether the ANSI_Padding setting is ON or OFF across all columns on the table. In latter day versions of SQL server this is mostly ON by default, but some developers may customise certain columns to have ANSI_PADDING set to off. If you have a mixed setup like this its best to move the data to another table with ANSI_PADDING set to ON. Try using the same UNPIVOT query on that table and it should work. Check ANSI_Padding Status

    SELECT name
    ,CASE is_ansi_padded 
    WHEN 1 THEN 'ANSI_Padding_On' 
    ELSE  'ANSI_Padding_Off'
    AS [ANSI_Padding_Check]
                      FROM sys.all_columns
    WHERE object_id = object_id('yourschema.yourtable')

Many situations be better suited for CROSS APPLY VALUES. It all depends on you, the jockey to choose horses for courses.

Cheers.