Join (Union) Two Columns in a Data View Web Part

Question

I need to join two columns from the same list in a data view web part.

Sample List:

Player1  Player2  Points
Jack     Ben      5
Jack     Tom      3
Tom               2

So the result from the data view web part would be:

Player   Points
Jack     5
Jack     3
Ben      5
Tom      3
Tom      2

Is this possible? I was able to easily get Player1 and Points to display in the web part, but I cannot figure out how to add the Player2 column data to the end of the existing list.

Any help would be greatly appreciated!

---

Additional Info:

I want to join the two Player columns and then group by Player. Jack in row one and two of the Sample List is the same person (Tom in row two and three is also the same person).

If I was able to get the Player1 and Player2 columns joined into one column, I would then like to group the results by Player (and then I have the DVWP summing the points for each player).

Answer 1

You could also simply use custom XSLT in the rowview template of the DVWP that displays the player1 name and associated points. Then performs a test if Player2 is not null, then display player2 and associated points. Then apply a sort alphabetically.

Answer 2

If you are using SharePoint Designer then please have a look at this link.

http://blah.winsmarts.com/2007-10-performing_joins_between_sharepoint_lists.aspx

Answer 3

Not sure if you can treat the List rows as SQL, but if you can you can do;

Select Player1 as Player, Points from ListTable UNION Select Player2 as Player, Points from ListTable WHERE IsNull(Player2, 0 ) <> 0