Join Values from 2 columns

Question

Post reply

Join Values from 2 columns

davdam8

Hall of Fame

Points: 3620
More actions
January 22, 2013 at 6:16 am

#269302

Hi geniuses,
I have a table which contains groups inside Receivers and Payers (2 columns).
Some groups can be Receivers and Payers, for instance , A and B.
B, D and E are only payers.
For the receivers we got A, B and C.
For the payers we have A, B, D and E.
Receivers \\ Payers
A \\ A
B \\ A
A \\ B
A \\ D
B \\ D
C \\ A
B \\ E
A \\ E
How do I create a column 'Group' that contains both Receivers and Payers, not repeated?
What i need:
Group
A
B
C
D
E
Thanks, Regards

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

SELECT Receivers FROM myTable

UNION

SELECT Payers FROM myTable

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 2

I think this is what you're looking for...

Providing easily consumable sample data makes it easier for people to help and more likely...

--Setup Sample Data

declare @transactions as table (Receivers char(1), Payers char(1))

insert into @transactions(Receivers, Payers)

values('A' , 'A')

,('B' , 'A')

,('A' , 'B')

,('A' , 'D')

,('B' , 'D')

,('C' , 'A')

,('B' , 'E')

,('A' , 'E')

--Actual work statement

select distinct [Group]

from (

select Receivers [Group]

from @transactions

union all

select Payers [Group]

from @transactions

) c

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

davdam8 Hall of Fame Points: 3620 More actions · Answer 3

Thanks Very Much! Helped a lot.

Could you geniuses take a look at this topic:

http://www.sqlservercentral.com/Forums/Topic1409896-391-1.aspx

Thanks again.

Best regards

davdam8 Hall of Fame Points: 3620 More actions · Answer 4

It is possible to add a Group By inside each Select?

If yes how do you do it?

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

I suspect your definition of 'group by' might be different than mine. Using the sample data in your first post how would the resultset look if it did a "Group By inside each Select"?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

Hers's another way.

declare @transactions as table (Receivers char(1), Payers char(1))

insert into @transactions(Receivers, Payers)

values('A' , 'A')

,('B' , 'A')

,('A' , 'B')

,('A' , 'D')

,('B' , 'D')

,('C' , 'A')

,('B' , 'E')

,('A' , 'E')

SELECT RecPyr

FROM @transactions

CROSS APPLY (VALUES (Receivers),(Payers)) a(RecPyr)

GROUP BY RecPyr

Now what did you want to do with a GROUP BY inside of the SELECT?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St