Help in formulating simple SQL select statement/Pivot

[ausmoran]

I am having difficulty in formulating a relatively simple select statement. I have a member table (parent) which is related to a designation table (child) in which there are multiple related records. I would like to be able to pull the chilren in a single row (record) but am not sure how to accomplish this. Can someone give me a hand with this? Thanks so much in advance...THANKS!

Here is the select statement that I have now, but it pulls a member name each time they have a new designation (example: if a member has several designations, then they end up with two or three records. I would like to have one records with several designation fields).

select Member.Last_Name, Member.First_Name, Member_Designations.Designations
From Member, Member_Designations
Where Member.Member_Number=Member_Designations.Member_Number
Order By Last_Name Asc

Thanks again!!!!

Austin

 

[19511950]

select distinct A.Last_Name, A.First_Name
From Member A, Member_Designations B
Where A.Member_Number=B.Member_Number
Order By A.Last_Name Asc

 

[Eyespi20]

MMMM 19511950, that's still going to give him multiple rows for each name.

Is there a fixed number of designation types? Can you create a table with Member_Number, Desig1, Desig2, desig3....etc? Then you do a case statement to fill it up.

Margaret

 

[ausmoran]

I'm afraid this might be soooo far over my head that I might just plain be lost, here! There are a total of about twenty designations. As for making additional tables, I'm not sure I'm up to the task.

Thanks for your kind help.

Austin

 

[jhall01]

I think what Margaret was saying is:

How many child records can you have per parent?

Will the number of child records change dynamically per parent?

Is there a maximum number of child records per parent?

If you have a static number of child records or a maximum number then you could add a field to the child table that creates a category indicator, then transform the records into columns based on the category.

If you could answer the above

 

[ausmoran]

Yes, thank you all so much for you helpfulness! There are 19 different designations (though not all members have a designation - which I realize I have not considered in my select statement). Thanks again for your help. Unfortunately, I cannot physically add anything to the SQL table.

Austin

 

[jhall01]

well if each desgnation has a unique identifier(category) then you could use a case statement:

tParent:
PKguid col1 col2
1 Y N
2 Y N
3 y n

tChild:
FKguid desigcol desccol
1 Des1 blah1
1 Des2 blah2
1 Des3 blah3
2 Des1 blah1
3 Des1 blah1

[blue]SELECT[/blue]
  a.PKguid, a.col1, a.col2,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des1' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation1,
  [blue]CASE WHEN[/blue] b.designation = 'Des1' [blue]THEN[/blue] b.desccol [blue]ELSE NULL END[/blue] description1,
  [blue]CASE[/blue] WHEN b.desigcol = 'Des2' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation2,
  [blue]CASE WHEN[/blue] b.designation = 'Des2' [blue]THEN[/blue] b.desccol [blue]ELSE NULL END[/blue] description2,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des3' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation3,
  [blue]CASE WHEN[/blue] b.designation = 'Des3' [blue]THEN [/blue]b.desccol [blue]ELSE NULL END[/blue] description3,
  ...
  [blue]CASE WHEN[/blue] b.desigcol = 'Des19' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation19,
  [blue]CASE WHEN[/blue] b.designation = 'Des19' [blue]THEN b.desccol [blue]ELSE NULL END[/blue] description19
[blue]FROM[/blue]
  tParent a
  [blue]LEFT OUTER JOIN[/blue]
  tChild b
  [blue]ON[/blue]
  a.PKguid = b.FKguid

This would take care of your transformation if you have a unique category indicator for each child record for a given parent record

 

[jhall01]

Corrected code:

[blue]SELECT[/blue]
  a.PKguid, a.col1, a.col2,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des1' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation1,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des1' [blue]THEN[/blue] b.desccol [blue]ELSE NULL END[/blue] description1,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des2' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation2,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des2' [blue]THEN[/blue] b.desccol [blue]ELSE NULL END[/blue] description2,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des3' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation3,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des3' [blue]THEN [/blue]b.desccol [blue]ELSE NULL END[/blue] description3,
  ...
  [blue]CASE WHEN[/blue] b.desigcol = 'Des19' [blue]THEN[/blue] b.desigcol [blue]ELSE NULL END[/blue] designation19,
  [blue]CASE WHEN[/blue] b.desigcol = 'Des19' [blue]THEN [/blue] b.desigcol [blue]ELSE NULL END[/blue] description19
[blue]FROM[/blue]
  tParent a
  [blue]LEFT OUTER JOIN[/blue]
  tChild b
  [blue]ON[/blue]
  a.PKguid = b.FKguid

you can add or remove more case statements depending on the number of columns you need to transform for each child record.

Jon

 

[ausmoran]

Wow, Thanks so much for you help. I will play around with it this evening and see if I can figure it out. I am a novice at this, so I may require a little more info if you don't mind. You guys have been very kind. Thanks again.

Austin