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 ?
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)
| 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.