Failed to Convert Columns to Rows When Table has Different Data Types

Question

I have a table like below:

Create table EmpDetails
(
    ID int primary key,
    DOB datetime not null,
    Name nvarchar(100) not null,
    Salary float not null,
    Email nvarchar(50) not null,
    IsActive bit not null
)

Insert into EmpDetails VALUES (2134, '1985-09-19 03:37:21.757', 'Jack Sparrow', 75000,'abc@gmail.com', 1)

When I tried to convert the columns into Rows using below query I am getting error and the query failed.

select ID, Attrib, Value
from EmpDetails
cross apply
(
select 'DOB', DOB union all
select 'Name', Name union all
select 'Salary', Salary union all
select 'Email', Email union all
select 'IsActive', IsActive
)empd(Attrib, Value);

EDIT: I have also tried below, but got same error

select ID, Attrib, Value
from EmpDetails
cross apply
(
values
('DOB', DOB),
('Name', Name),
('Salary', Salary),
('Email', Email),
('IsActive', IsActive)
) empd(Attrib, Value);

Error:

Conversion failed when converting date and/or time from character string.

When I tried to CAST the DOB field as datetime still I am getting same error:

select 'DOB', CAST(DOB as datetime)

How can I proceed here ? Any help is appreciated.

M.Ali · Accepted Answer · 2014-09-18T23:11:02.200

Since you are doing a union all the data type should match. try the following....

select ID, Attrib, Value
from EmpDetails
cross apply
(
select 'DOB'     , CONVERT(NVARCHAR(50),DOB , 121) union all
select 'Name'    , CAST(Name AS NVARCHAR(50))      union all
select 'Salary'  , CAST(Salary AS NVARCHAR(50))    union all
select 'Email'   , CAST(Email AS NVARCHAR(50))     union all
select 'IsActive', CAST(IsActive AS NVARCHAR(50))
)empd(Attrib, Value);

OR

SELECT ID
       ,Attrib
       ,Value
 FROM 
(
    SELECT ID 
           ,CONVERT(NVARCHAR(50),DOB , 121)  AS DOB
           ,CAST(Name AS NVARCHAR(50))       AS Name
           ,CAST(Salary AS NVARCHAR(50))     AS Salary
           ,CAST(Email AS NVARCHAR(50))      AS Email
           ,CAST(IsActive AS NVARCHAR(50))   AS IsActive
    FROM EmpDetails 
 )t
 UNPIVOT (Value FOR Attrib IN (DOB, Name, Salary, Email, IsActive) )up

Result

╔══════╦══════════╦═════════════════════════╗
║  ID  ║  Attrib  ║          Value          ║
╠══════╬══════════╬═════════════════════════╣
║ 2134 ║ DOB      ║ 1985-09-19 03:37:21.757 ║
║ 2134 ║ Name     ║ Jack Sparrow            ║
║ 2134 ║ Salary   ║ 75000                   ║
║ 2134 ║ Email    ║ abc@gmail.com           ║
║ 2134 ║ IsActive ║ 1                       ║
╚══════╩══════════╩═════════════════════════╝

UPDATE

For values syntax again you will need to cast/convert all column values to one data type something like this...

 select ID, Attrib, Value
from EmpDetails
cross apply
(
values
('DOB'     , CONVERT(NVARCHAR(50),DOB , 121)),
('Name'    , CAST(Name AS NVARCHAR(50)) ),
('Salary'  , CAST(Salary AS NVARCHAR(50))),
('Email'   , CAST(Email AS NVARCHAR(50))),
('IsActive', CAST(IsActive AS NVARCHAR(50)))
) empd(Attrib, Value);

why not cast them all to the same datatype instead of some as `varchar` and some as `nvarchar`? — Taryn, Sep 18 '14 at 22:44
The downvote is from me because a) you do not explain why the OP's code is not working, the "all the data type should match" is wrong and b) your code does not do what you preach (as bluefeet noted). — ypercubeᵀᴹ, Sep 18 '14 at 22:53
@M.Ali Thanks for the answer. However, if I use values(...) instead of union all, I am getting the same error. Is it due to the same reason ? I have updated the question — skjcyber, Sep 18 '14 at 23:03
Even if you are using `VALUE Syntax` you will need to convert all the columns to a same data type. It will fix the issue. — M.Ali, Sep 18 '14 at 23:08

Failed to Convert Columns to Rows When Table has Different Data Types

1 Answers1

OR

Result

UPDATE