Complex Join and Group By

Question

Post reply

Complex Join and Group By

hkflight

SSCommitted

Points: 1949
More actions
November 23, 2011 at 1:53 pm

#246745

I am having difficulty with join logic. I have a table with multiple rows per organization. The reason for this is to accomodate multiple org contacts per organization. Another assumption here is that that all contact types are defined and there can only be one contact oer contact type per organization.
CREATE TABLE orgcontacts
organization (varchar, null),
contact (varchar, null),
contacttype (varchar, null),
So I have something like:
Organization Contact ContactType
org1 Jim Type1
org1 Bob Type2
org2 Tim Type2
org3 Joe Type1
org3 Dan Type3
What I need is:
Organization Contact ContactType1 Contact ContactType2 Contact ContactType3
org1 Jim Type1 Bob Type2
org2 Tim Type2
org3 Joe Type1 Dan Type3
I tried something like:
select type1.organization, type1.contact,type1.contacttype as ContactType1,
type2.contact,type2.contacttype as ContactType2,
type3.contact,type3.contacttype as ContactType3
from orgcontacts as type1
join orgcontacts as type2
on type1.organization=type2.organization
join orgcontacts as type3
on type1.organization=type3.organization
group by type1.organization, type1.contact,type1.contacttype, type2.contact,type2.contacttype, type3.contact,type3.contacttype
But this gives me multiple rows per org. I also tried adding a where clause, but haven't had any luck. Any help would be appreciated, I am new to this type of work.
Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Moses Smith Valued Member Points: 54 More actions · Answer 1

I'm still confused on what you need exactly from your query.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

The output you want is commonly referred to as a PIVOT or cross-tab ("crosstab"). If you'll search this site using those terms, you'll find plenty of examples.

I am assuming there is a small, fixed number of types of contacts. You can't do a variable number of columns with either PIVOT or crosstab'ing without resorting to dynamic SQL.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

hkflight SSCommitted Points: 1949 More actions · Answer 3

Yes, exactly. There is a small number of predetermined contact types. Thank you for the hint on the proper search terms!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

You're welcome. I'm sure you'll find enough to get you started, but don't hesitate to come back if you have specific questions.

By the way, PIVOT is an actual T-SQL statement. "Crosstab" is just the name of a technique.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Articles on pivots, crosstabs, and dynamic crosstabs...

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)