December 15, 2012 at 3:57 pm
I have a small problem. I have a table calles students. It has mother, father and parent_guardian in it. I am importing data into another program that only has 1 parents field. So I need 3 lines per each student. A line for mother, father, then parent_guardian.
select s.mother,s.father, s.parent_guardian, s.first_name, s.last_name,s.id
from students s
where s.enroll_status = 0
Thanks in advance!
December 15, 2012 at 4:39 pm
pkaraffa (12/15/2012)
I have a small problem. I have a table calles students. It has mother, father and parent_guardian in it. I am importing data into another program that only has 1 parents field. So I need 3 lines per each student. A line for mother, father, then parent_guardian.
select s.mother,s.father, s.parent_guardian, s.first_name, s.last_name,s.id
from students s
where s.enroll_status = 0
You could select three times and union the results together something like:
SELECT s.mother AS parent,
s.first_name,
s.last_name,
s.id
FROM students s
WHERE s.enroll_status = 0
UNION ALL
SELECT s.father AS parent,
s.first_name,
s.last_name,
s.id
FROM students s
WHERE s.enroll_status = 0
UNION ALL
SELECT s.guardian AS parent,
s.first_name,
s.last_name,
s.id
FROM students s
WHERE s.enroll_status = 0
ORDER BY s.Id, parent;
You could also probably unpivot the columns to get seperate rows.
HTH,
Rob
December 15, 2012 at 4:53 pm
You could also use an INNER JOIN to a hard-coded table:
select
case parents.whichParent when 'father' then s.father when mother then s.mother else s.parent_guardian end,
s.first_name, s.last_name,s.id
from students s
inner join (
select 'father' as whichParent union all
select 'mother' union all
select 'parent_guardian'
) as parents on
(parents.whichParent = 'father' AND s.father > 0) OR
(parents.whichParent = 'mother' AND s.mother > 0) OR
(parents.whichParent = 'parent_guardian' AND s.parent_guardian > 0)
where
s.enroll_status = 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 15, 2012 at 7:47 pm
You can skip joins altogether. While you could certinaly use the VALUES statement in 2008 and above, I decided to stick to a method that will work in all versions from SQL Server 2005 and up.
--===== Return a full "line" for each relation present
SELECT s.ID, s.First_Name, s.Last_Name, ca.Relation, ca.Relation_Name
FROM dbo.Students s
CROSS APPLY
(
SELECT 'Mother', Mother UNION ALL
SELECT 'Father', Father UNION ALL
SELECT 'Guardian', Parent_Guardian
) ca (Relation, Relation_Name)
WHERE ca.Relation_NAME > '' --Is NOT NULL or BLANK
;
For future posts, please read the article at the first link in my signature line below. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2012 at 5:50 am
Thank you all. Appreciate all of your knowledge. Thanks again!
December 16, 2012 at 10:07 am
pkaraffa (12/16/2012)
Thank you all. Appreciate all of your knowledge. Thanks again!
You're welcome.
As a bit of a side bar, you should store the data as 1 row per relationship anyway. It is certainly possible for a child to have more than 3 authorized legal relationships such as biological Mother/Father, Step Mother/Father, and as many authorized guardians (trusted babysitters / caretakers / Aunts / Uncles / Grandparents / older siblings, etc) as someone could imagine. While it's normal to have just the two or three that you currently have, your current system isn't normalized to easily take care of other frequent occuring contingencies.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply