tsql row to column (pivot)

Question

i have a table with the following information

CREATE TABLE [dbo].[HR_DEPENDENTS](  
            [PARENT_ID]     [bigint]       NOT NULL,  
            [DEPENDENT_ID]  [bigint]       NOT NULL,  
            [LAST_NAME]     [varchar](100) NOT NULL,  
            [FIRST_NAME]    [varchar](100)     NULL,  
            [DATE_OF_BIRTH] [date]             NULL)  

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)  
values (100, 1, 'Ray', 'First Child',cast('06/01/2001' as date))  

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)  
values (100, 1, 'Ray', 'Second', cast('06/01/2002'as date))  

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)  
values (100, 1, 'Ray', 'Third',cast('06/01/2003' as date))  

I used the sql below.

SELECT t01.parent_id  
    ,t01.taxdepn1  
    ,t01.taxdepn2  
    ,t01.taxdepn3  
    ,t01.taxdepn4  
    ,t02.depn1bday  
    ,t02.depn2bday  
    ,t02.depn3bday  
    ,t02.depn4bday  
FROM (SELECT PARENT_ID  
    ,[1] as taxdepn1  
    ,[2] as taxdepn2  
    ,[3] as taxdepn3  
    ,[4] as taxdepn4  
    FROM ( SELECT PARENT_ID  
         , dependent_id  
         , first_name+' '+last_name as fullname  
         FROM  dbo.hr_dependents 
         ) AS piv  
    PIVOT ( max(fullname)  
            FOR dependent_id IN ([1], [2], [3], [4])  
          ) AS chld  
    ) T01
    ,(SELECT PARENT_ID2  
            , [1] as depn1bday  
            , [2] as depn2bday  
            , [3] as depn3bday  
            , [4] as depn4bday  
      FROM ( SELECT PARENT_ID as parent_id2  
                   ,dependent_id  
                   ,date_of_birth  
             FROM dbo.hr_dependents ) AS piv1  
      PIVOT ( min(date_of_birth)  
              FOR dependent_id IN ([1], [2], [3], [4])  
            ) AS chld1  
      ) T02  
WHERE T01.PARENT_ID=T02.PARENT_ID2  

My worry is I may get the wrong date_of_birth of a particular dependent child.
I'm new to sqlserver and I'm using sqlexpress (2008).

Any help is highly appreciated...

Thank You
Elmer

Answer 1

Hi
Correction on the insert statement

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 1, 'Ray', 'First Child',cast('06/01/2001' as date))

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 2, 'Ray', 'Second', cast('06/01/2002'as date))

insert into HR_DEPENDENTS (PARENT_ID, DEPENDENT_ID, LAST_NAME, first_name, date_of_birth)
values (100, 3, 'Ray', 'Third',cast('06/01/2003' as date))

year for date_of_birth differs by 1 year.

Regards,
Elmer