Father / Mother query 2

[pwills]

My head will explode if I don't crack this soon. I have table like:

Child Father Mother
Bill Bob Jane
Joe Mike Mary

I need the view:

Child Name Relation
Bill Bob Father
Bill Jane Mother
Joe Mike Father
Joe Mary Mother

Can anyone help?

 

[MeanGreen]

Try this:

select child,father as [name],'father' as relation from table
union
select child,mother as [name],'mother' as relation from table
order by child,parent

Hope this helps.

 

[tlbroadbent]

I would include ALL on the UNION to prevent SQL Server from sorting and eliminating duplicates. Also, Order By Name rather than Parent or use Parent as the column name in the query as I have done below.

Select Child, father as Parent, 'Father' as Relation
from table

Union All

Select Child, mother, 'Mother' as Relation
From table
Order By child, parent
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.