Rows into Columns by using pivot

Question

Can you help me to solve this error

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

Table structure:

CREATE TABLE [dbo].[tblHRIS_ChildDetails](
    [intCHID] [int],
    [intSID] [int] NOT NULL,
    [nvrChildname] [nvarchar](250) NULL,
    [nvrChildGender] [nvarchar](50) NULL,
    [dttChildDOB] [datetime] NULL,
    [nvrnominee] [nvarchar](50) NULL,
    [nvrChildOccupation] [nvarchar](250) NULL,
    [dttCreatedon] [datetime] NULL,
    [dttModifiedOn] [datetime] NULL,
    [nvrModifiedby] [nvarchar](50) NULL
) ON [PRIMARY]

Query:

select *
from
(
  select value, col+'_'+cast(rn as varchar(10)) col
  from
  (
    select nvrchildname,
      nvrchildgender,
      convert(nvarchar(10), dttchildDOB, 120) dttchildDOB,
      nvrchildoccupation,
      row_number() over(partition by intsid order by intCHID) rn
    from tblHRIS_ChildDetails
    where intsid = 463
  ) src
  unpivot
  (
    value 
    for col in (nvrchildname, nvrchildgender, dttchildDOB,  nvrchildoccupation)
  ) unpiv
) src1
pivot
(
  max(value)
  for col in ([nvrchildname_1], [nvrgender_1], 
              [dttchildDOB_1], [occupation_1], 
              [nvrchildname_2], [nvrgender_2], 
              [dttchildDOB_2], [occupation_2]) 
) piv

I cannot to able to run this query i think some cast problem pls resolve.

Sql fiddle

possible duplicate of [rows into columns](http://stackoverflow.com/questions/13599404/rows-into-columns) — Taryn, Nov 29 '12 at 10:56

score 0 · Answer 1 · answered Nov 29 '12 at 10:24

0

The columns in the UNPIVOT list:

nvrchildname, nvrchildgender, dttchildDOB,  nvrchildoccupation

Have different data types.

You have to ensure that the types of the columns. Something like :

 ...
  CAST(nvrchildname AS NVARCHAR(10)) nvrchildname,
  CAST(nvrchildgender AS NVARCHAR(10)) nvrchildgender,
  convert(nvarchar(10), dttchildDOB, 120) dttchildDOB,
  CAST (nvrchildoccupation AS NVARCHAR(10)) nvrchildoccupation
 ...

Updated SQL Fiddle Demo

answered Nov 29 '12 at 10:24

Mahmoud Gamal

78,257
17
139
164

@mahmound where gender there but it returns null check it – Kajah User Nov 29 '12 at 10:27
Check gender and childocupation is missing – Kajah User Nov 29 '12 at 10:29
Hi if u run this select nvrchildname, cast(nvrchildgender as nvarchar(250)) nvrchildgender, convert(nvarchar(250), dttchildDOB, 120) dttchildDOB, nvrchildoccupation, row_number() over(partition by intsid order by intCHID) rn from tblHRIS_ChildDetails where intsid = 463 you will get gender and occupation but it is not returning in the Fiddle did u check that – Kajah User Nov 29 '12 at 10:33

RichardTheKiwi · Accepted Answer · 2012-11-29T10:31:35.123

To UNPIVOT successfully, the column types must match. Since two of the 4 columns are already nvarchar(250) and the other two shorter, promoting them to nvarchar(250) is the most expedient fix.

select *
from
(
  select value, col+'_'+cast(rn as varchar(10)) col
  from
  (
    select nvrchildname,
      cast(nvrchildgender as nvarchar(250)) nvrgender,
      convert(nvarchar(250), dttchildDOB, 120) dttchildDOB,
      nvrchildoccupation occupation,
      row_number() over(partition by intsid order by intCHID) rn
    from tblHRIS_ChildDetails
    where intsid = 463
  ) src
  unpivot
  (
    value 
    for col in (nvrchildname, nvrgender, dttchildDOB, occupation)
  ) unpiv
) src1
pivot
(
  max(value)
  for col in ([nvrchildname_1], [nvrgender_1], 
              [dttchildDOB_1], [occupation_1], 
              [nvrchildname_2], [nvrgender_2], 
              [dttchildDOB_2], [occupation_2]) 
) piv

Note: You have named the PIVOT column nvrgender but the base column was nvrchildgender. Same problem with mismatched occupation column naming between pivot and unpivot.

Hi if u run this select nvrchildname, cast(nvrchildgender as nvarchar(250)) nvrchildgender, convert(nvarchar(250), dttchildDOB, 120) dttchildDOB, nvrchildoccupation, row_number() over(partition by intsid order by intCHID) rn from tblHRIS_ChildDetails where intsid = 463 you will get gender and occupation but it is not returning in the form — Kajah User, Nov 29 '12 at 10:31

Rows into Columns by using pivot

2 Answers2

Updated SQL Fiddle Demo