?
Solved

SQL help in generating data

Posted on 2009-05-03
9
Medium Priority
?
306 Views
Last Modified: 2012-05-06
Hi,
I need help in generating data from this table:
Ex.
StudentName               ParentName                     Relation
Bill Doe                         John Doe                         Father
Bill Doe                         Jane Doe                         Mother

and output it as  
StudentName               Father                              Mother
Bill Doe                        John Doe                          Jane Doe

Any help?
0
Comment
Question by:ronpangan
9 Comments
 
LVL 6

Expert Comment

by:mvgeertruyen
ID: 24290175
This could be one way to do it:
SELECT DISTINCT
a.Studentname,
b.ParentName AS Father,
c.ParentName AS Mother
FROM [students] a
INNER JOIN [students] b on a.[Studentname] = b.[Studentname] and b.Relation = 'Father'
INNER JOIN [students] c on a.[Studentname] = c.[Studentname] and c.Relation = 'Mother'

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 24290190
What key(s) exist in your table? It helps if you tell us that when you post a question such as this, otherwise we can only guess. I'll assume (StudentName, Relation) is unique. Also, can we assume that every Student has exactly two parents? Try this:

SELECT F.StudentName,
F. ParentName AS Father,
M. ParentName AS Mother
FROM tbl AS F
INNER JOIN tbl AS M
ON F.Relation = 'Father'
AND M.Relation = 'Mother'
AND F.StudentName = M.StudentName ;

0
 
LVL 44

Expert Comment

by:GRayL
ID: 24290768
Use a crosstab query:

TRANSFORM First(ParentName)
SELECT StudentName FROM tblParents
GROUP BY StudentName
PIVOT Relation;


0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
LVL 44

Expert Comment

by:GRayL
ID: 24290775
Of course, use your real table name in place of tblParents.  I wonder why you didn't think the table name important ;-)
0
 

Author Comment

by:ronpangan
ID: 24324503
Thanks for all your input.  Just wanted to clear that the data is only on one table
________________________________________________________
|  StudentName         |      ParentName           |          Relation                  |
------------------------------------------------------------------------------------
|      Bill Doe              |           John Doe           |              Father                |
------------------------------------------------------------------------------------  
|       Bill Doe             |            Jane Doe         |                Mother              |
------------------------------------------------------------------------------------
and output it as  
________________________________________________________
|     StudentName     |            Father              |                Mother              |
------------------------------------------------------------------------------------
|          Bill Doe         |               John Doe      |                    Jane Doe       |
------------------------------------------------------------------------------------

The table is being maintained by a 3rd party vendor and we just need to query the information and display it as the table above.  Thanks everyone.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24327342
Did you try my cross tab query?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 24327410
With this table tblNames

StudentName      ParentName      Relation
Bill Doe      John Doe                      Father
Bill Doe      Mary Doe                      Mother

Running this query:

TRANSFORM First(ParentName)
SELECT StudentName FROM tblNames
GROUP BY StudentName
Pivot Relation;

Yielded this result:

StudentName      Father            Mother
Bill Doe               John Doe              Mary Doe
0
 

Author Closing Comment

by:ronpangan
ID: 31577327
GrayL, you are a genius.  Thanks for providing a clear solution.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24331662
Thanks, glad to help.

0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question