Solved

# SQL help in generating data

Posted on 2009-05-03
Medium Priority
306 Views
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
Question by:ronpangan

LVL 6

Expert Comment

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'
``````
0

LVL 22

Expert Comment

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

ID: 24290768
Use a crosstab query:

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

0

LVL 44

Expert Comment

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

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

ID: 24327342
Did you try my cross tab query?
0

LVL 44

Accepted Solution

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

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

LVL 44

Expert Comment

ID: 24331662

0

## Featured Post

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.