Error : The type of column "DOB" conflicts with the type of other columns specified in the UNPIVOT list

Question

MY QUERY,

simply to convert the row into column

SELECT
  ColumnName,
  value
FROM (SELECT
  id [ID],
  firstname [First Name],
  lastname [Last Name],
  dob [DOB],
  sex [Gender]
FROM client
WHERE id = '11') d
UNPIVOT
(
Value FOR
ColumnName IN ([ID], [First Name], [Last Name], [DOB], [Gender])
) unpiv;

But it showing an error.

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

Answer 1

As the result will bring back all columns in rows, building a new derived column with all the values, you must ensure, that the types fit together.

You can wrap all your columns in CAST

SELECT
  ColumnName,
  value
FROM (SELECT
  CAST(id AS NVARCHAR(MAX)) [ID],
  CAST(firstname AS NVARCHAR(MAX)) [First Name],
  CAST(lastname AS NVARCHAR(MAX)) [Last Name],
  CAST(dob AS NVARCHAR(MAX)) [DOB],
  CAST(sex AS NVARCHAR(MAX)) [Gender]
FROM client
WHERE id = '11') d
UNPIVOT
(
Value FOR
ColumnName IN ([ID], [First Name], [Last Name], [DOB], [Gender])
) unpiv;

The DOB will be converted to the default setting of your machine. Using CONVERT you might enforce a given date/time format.