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.

Answer 1

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);