tsql row to column (pivot)

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

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

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

Assuming that the dependent id changes for each new set of kids for each parentid, you won't get the wrong date of birth associated with the wrong name. When you are pivoting the table, you are assigning a value to a row for that parent to a given column that is unique and consistent between pivots.

In other words, the column name [1] represents dependentid value 1; that doesn't change from table to table, so when you pivot the second time, the firstname for row 1 in the source table is associated with the birth_date for row 1.

I would be tempted to use the row_number() function to determine a birth order instead of relying on a dependent id, but as long as your business logic enforces consistency, there's no real advantage.