WITH PacientesDiagnosticos AS(
    SELECT DISTINCT TOP 50
        p.IdPaciente      ,
        a.IdAtencion as IdAtencion,
        (p.ApellidoPaterno+' '+p.ApellidoMaterno+' '+p.PrimerNombre) as Nombres,
        --esto de abajo estuve avanzando para sacar las fechas o nose si esta bien :s
        --DENSE_RANK() OVER (PARTITION BY a.IdAtencion ORDER BY a.FechaIngreso) AS NumFechaIngreso,
        a.FechaIngreso as FechaIngreso,
        a.FechaEgreso as FechaEgreso,
        d.Descripcion AS Dignostico,
        DENSE_RANK() OVER( PARTITION BY a.IdAtencion ORDER BY d.Descripcion) AS NumDiagnosticos
    FROM Pacientes p
    JOIN Atenciones a ON p.IdPaciente = a.IdPaciente
    JOIN AtencionesDiagnosticos ad ON a.IdAtencion = ad.IdAtencion
    JOIN Diagnosticos d ON ad.IdDiagnostico = d.IdDiagnostico
)
SELECT IdPaciente      ,
        IdAtencion,
       Nombres,
       FechaIngreso,
       FechaEgreso,
       MAX( CASE WHEN NumDiagnosticos = 1 THEN Dignostico END) AS Dignostico1,
       MAX( CASE WHEN NumDiagnosticos = 2 THEN Dignostico END) AS Dignostico2,
       MAX( CASE WHEN NumDiagnosticos = 3 THEN Dignostico END) AS Dignostico3,
       MAX( CASE WHEN NumDiagnosticos = 4 THEN Dignostico END) AS Dignostico4,
       MAX( CASE WHEN NumDiagnosticos = 5 THEN Dignostico END) AS Dignostico5,
       MAX( CASE WHEN NumDiagnosticos = 6 THEN Dignostico END) AS Dignostico6
FROM PacientesDiagnosticos
GROUP BY IdPaciente,
        IdAtencion,
       Nombres,
       FechaIngreso,
       FechaEgreso;

ROW_NUMBER() OVER (PARTITION BY p.IdPaciente, ORDER BY FechaIngreso) AS RN

FROM PacientesDiagnosticos T1
    LEFT JOIN PacientesDiagnosticos T2
        ON T2.IdPaciente = T1.IdPaciente
        AND T2.RN = T1.RN - 1

WITH PacientesDiagnosticos AS(
    SELECT DISTINCT TOP 50
            p.IdPaciente      ,
            a.IdAtencion as IdAtencion,
            (p.ApellidoPaterno+' '+p.ApellidoMaterno+' '+p.PrimerNombre) as Nombres,
            --esto de abajo estuve avanzando para sacar las fechas o nose si esta bien :s
            --DENSE_RANK() OVER (PARTITION BY a.IdAtencion ORDER BY a.FechaIngreso) AS NumFechaIngreso,
            a.FechaIngreso as FechaIngreso,
            a.FechaEgreso as FechaEgreso,
            d.Descripcion AS Dignostico,
            DENSE_RANK() OVER (PARTITION BY a.IdAtencion ORDER BY d.Descripcion) AS NumDiagnosticos,
        ROW_NUMBER() OVER (PARTITION BY p.IdPaciente, ORDER BY FechaIngreso) AS RN
    FROM Pacientes p
    JOIN Atenciones a ON p.IdPaciente = a.IdPaciente
    JOIN AtencionesDiagnosticos ad ON a.IdAtencion = ad.IdAtencion
    JOIN Diagnosticos d ON ad.IdDiagnostico = d.IdDiagnostico
)
SELECT  T1.IdPaciente,
    T1.IdAtencion,
    T1.Nombres,
    T1.FechaIngreso,
    T2.FechaIngreso,
    T1.FechaEgreso,
    MAX( CASE WHEN T1.NumDiagnosticos = 1 THEN T1.Dignostico END) AS Dignostico1,
    MAX( CASE WHEN T1.NumDiagnosticos = 2 THEN T1.Dignostico END) AS Dignostico2,
    MAX( CASE WHEN T1.NumDiagnosticos = 3 THEN T1.Dignostico END) AS Dignostico3,
    MAX( CASE WHEN T1.NumDiagnosticos = 4 THEN T1.Dignostico END) AS Dignostico4,
    MAX( CASE WHEN T1.NumDiagnosticos = 5 THEN T1.Dignostico END) AS Dignostico5,
    MAX( CASE WHEN T1.NumDiagnosticos = 6 THEN T1.Dignostico END) AS Dignostico6
    FROM PacientesDiagnosticos T1
    LEFT JOIN PacientesDiagnosticos T2
        ON T2.IdPaciente = T1.IdPaciente
        AND T2.RN = T1.RN - 1
    GROUP BY T1.IdPaciente,
           T1.IdAtencion,
           T1.Nombres,
           T1.FechaIngreso,
           T2.FechaIngreso,
           T1.FechaEgreso;