Solved

SQL help in generating data

Posted on 2009-05-03
9
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 125 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

717 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