Consulta en Ms-Sql

[websistems]

Necesito obtener esta informacion de 2 tablas:

PAdre|Hijomenor1|hijo2|hijo3|hijo4|


create table padre
(cod_padre int not null,
apellido varchar(50),
nombre varchar(50))


ALTER TABLE PADRE
ALTER COLUMN cod_padre int NOT NULL


ALTER TABLE padre
ADD PRIMARY KEY (cod_padre)



insert into padre (cod_padre,apellido,nombre) values (1001,'Perez','Juan')
insert into padre (cod_padre,apellido,nombre) values (1002,'Torres','Urias')
insert into padre (cod_padre,apellido,nombre) values (1003,'Vasquez','Jaime')
insert into padre (cod_padre,apellido,nombre) values (1004,'Mercado','Luis')


create table alu
(cod_alum int not null,
cod_padre int,
nombre varchar(250),
fecha_nac datetime,)

alter table alu
add primary key (cod_alum)

alter table alu
add foreign key (cod_padre) references padre(cod_padre)

insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (1,1001,'Carlos Perez',01/01/2001)
insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (2,1001,'Pedro Perez',01/01/2002)
insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (3,1001,'Rosa Perez',01/01/2000)
insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (4,1002,'Jose Torres',01/01/1999)
insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (5,1002,'Rosa Torres',01/01/2003)
insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (6,1003,'Juan Vasquez',08/01/2002)
insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (7,1003,'Maria Vasquez',01/09/2002)
insert into alu (cod_alum,cod_padre,nombre,fecha_nac) values (8,1004,'Olga Mercado',01/09/1997)

necesito saber el hijo menor del padre, y los demas hermanos, es decir

PADRE| HIJO_MENOR| HIJO2 | HIJO3 |

ojala puedan ayudarnme me demore bastante en crear las tablas
Se los agradezco de antemano

[Andres95]

una alternativa..

Código:

SELECT cod_padre
       , padre
       , ISNULL([1],'') hijo1
       , ISNULL([2],'') hijo1
       , ISNULL([3],'') hijo3
       , ISNULL([4],'') hijo4
       , ISNULL([5],'') hijo5
FROM (    
        SELECT  p.cod_padre,
                (p.nombre + ' ' + p.apellido) as Padre,
                a.nombre hijo, 
                ROW_NUMBER() OVER (PARTITION BY p.cod_padre
                                   ORDER BY  a.fecha_nac ) idhijo
        FROM padre p
        inner join alu a
        on p.cod_padre = a.cod_padre
      ) p    
PIVOT (    
        MIN (hijo)    
        FOR idhijo IN ([1], [2], [3],[4], [5] )    
      ) AS pvt    
    
order by cod_padre

Código:

Resultado:

cod_padre   padre         hijo1         hijo1         hijo3        hijo4  hijo5
----------- --------------------------- ------------- ------------ ------ ------
1001        Juan Perez    Rosa Perez    Carlos Perez  Pedro Perez         
1002        Urias Torres  Jose Torres   Rosa Torres                       
1003        Jaime Vasquez Maria Vasquez Juan Vasquez                      
1004        Luis Mercado  Olga Mercado                                          

(4 row(s) affected)

Saludos!

[websistems]

Andres95 muchas gracias la verdad me salio bien, excelente al solucion, no conocia la funcion pivot, pero solo un pequeño problemilla que espero me puedas ayudar el ejemplo lo simplifique pero necesito mostrar mas campos.

Adiciono los sig. campos a las tablas


alter table alu
add grado char(5)

alter table alu
add seccion char(1)

update alu set grado='1',seccion='a' where cod_alum=1
update alu set grado='2',seccion='b' where cod_alum=2
update alu set grado='3',seccion='c' where cod_alum=3
update alu set grado='2',seccion='a' where cod_alum=4
update alu set grado='3',seccion='b' where cod_alum=5
update alu set grado='4',seccion='b' where cod_alum=6
update alu set grado='2',seccion='c' where cod_alum=7
update alu set grado='3',seccion='a' where cod_alum=8


quiero mostrar lo mismo que en la consulta qu eme pasaste pero ademas saber en que grado y seccion estan cada uno de los hijos del socio, de verdad estoy tratando y me sale error



no los agrupa por el padre, como seria en este caso me podrias dar un amano por favor?

[Andres95]

podrias poner un ejemplo de como requieres los resultados?

Saludos!

[websistems]

Gracias por responder Andres95

Lo quisiera Asi

Cod_padre| Padre| Hijo1| GR| hijo2| GR| Hijo3| GR|
1001 | Juan Perez | Carlos Perez | 1 | Pedro Perez| 2 | Rosa Perez | 3 |
1002 | Urias Torres| Jose Torres | 4 |Rosa Torres | 5| | |
1003 | Jaime Vasquez| Juan Vasquez|3 |Maria Vasquez| 4|

Pero al intentar agregar el campo Grado (gr) me bota asi, tampoco lo puedo mostrar en la consulta este campo, podrias ayudarme por favor?

[websistems]

Bueno hasta esto pude hacer, como te comentaba en la respuesta anterior, tambien quiero mostrar el grado y seccion de los hijos en la consulta pero cuando en el select le agregaba para mostrar el campo grado, se desagrupaba y me salian repetidos los nombres de los padres despues de mucho trabajo a lo mas que he podido llegar es a esto: en la cual le sumo al nombre de lso hijos su grado y seccion quedandome de esta manera.

SELECT cod_padre
, padre
, ISNULL([1],'') hijo1
, ISNULL([2],'') hijo2
, ISNULL([3],'') hijo3
, ISNULL([4],'') hijo4
, ISNULL([5],'') hijo5
FROM (
SELECT p.cod_padre,
(p.nombre + ' ' + p.apellido) as Padre,
(a.nombre +' '+a.grado +''+ a.seccion) as hijo,
ROW_NUMBER() OVER (PARTITION BY p.cod_padre
ORDER BY a.fecha_nac ) idhijo
FROM padre p
inner join alu a
on p.cod_padre = a.cod_padre
) p
PIVOT (
MIN (hijo)
FOR idhijo IN ([1], [2], [3],[4], [5] )
) AS pvt
order by cod_padre



auque creo que no es lo optimo , pero hasta esto eh podido llegar, si me podrias ayudar te lo agradeceria

[websistems]

Lo he podido resolver de esta manera por si alguien le interesa.

Lo hice de esta manera.

SELECT CAST(RESULTADOS.COD_PADRE AS CHAR(5)) AS Cod_padre, cast(RESULTADOS.Padre as varchar(250)) as Padres, max(RESULTADOS.hijo1) as Hijo1,
max(RESULTADOS.GRADO1) as grado1,max(RESULTADOS.hijo2) as Hijo2,max(RESULTADOS.Grado2) as grado2,max(RESULTADOS.hijo3) as Hijo3,max(RESULTADOS.grado3) as Hijo3
FROM(
SELECT cod_padre
, padre
,HIJO1
,Grado1
,'' as HIJO2
,'' as Grado2
,'' as HIJO3
,'' as grado3
FROM (
SELECT p.cod_padre,
(p.nombre + ' ' + p.apellido) as Padre,
(a.nombre) AS HIJO1, (a.grado +''+ a.seccion) as GRADO1,
ROW_NUMBER() OVER (PARTITION BY p.cod_padre
ORDER BY a.fecha_nac ) idhijo
FROM padre p
inner join alu a
on p.cod_padre = a.cod_padre) P
WHERE idhijo=1

UNION

SELECT cod_padre
, padre
,'' AS HIJO1
,'' AS Grado1
,HIJO2
,Grado2
,'' as HIJO3
,'' as grado3
FROM (
SELECT p.cod_padre,
(p.nombre + ' ' + p.apellido) as Padre,
(a.nombre) AS HIJO2, (a.grado +''+ a.seccion) as GRADO2,
ROW_NUMBER() OVER (PARTITION BY p.cod_padre
ORDER BY a.fecha_nac ) idhijo
FROM padre p
inner join alu a
on p.cod_padre = a.cod_padre) P
WHERE idhijo=2

UNION
SELECT cod_padre
, padre
,'' AS HIJO1
,'' AS Grado1
,'' AS HIJO2
,'' AS Grado2
,HIJO3
,grado3
FROM (
SELECT p.cod_padre,
(p.nombre + ' ' + p.apellido) as Padre,
(a.nombre) AS HIJO3, (a.grado +''+ a.seccion) as GRADO3,
ROW_NUMBER() OVER (PARTITION BY p.cod_padre
ORDER BY a.fecha_nac ) idhijo
FROM padre p
inner join alu a
on p.cod_padre = a.cod_padre) P
WHERE idhijo=3) AS RESULTADOS
GROUP BY RESULTADOS.cod_padre,RESULTADOS.Padre
order by cod_padre


Resultado:



soucionado