Solved

SQL help in generating data

Posted on 2009-05-03
9
296 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

831 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