Solved

SQL help in generating data

Posted on 2009-05-03
9
294 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now