Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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).

share|improve this question
    
It looks to me like you are more interested in grouping than in a union. For example, it looks like you want to group players by the number of points, right? Will there ever be more than two players with the same number of points? Also, I'm assuming the two Jacks and two Toms are 4 different people. If not, then I am misunderstanding your logic and you may need to explain the context a little more. See if the group by functionality is what you need. –  Rob Wilson Nov 15 '10 at 21:12
    
Thanks for the additional info. Being a sports enthusiast my curiosity is getting the best of me. I haven't confirmed that this would work, but I wonder if a "linked data source" would do the trick. A DVWP will work with linked data sources, but I'm not sure what you can link (2 views of your list, other linked sources, unions, etc.). I am away from my computer now. I will open up SPD and consider alternatives first chance I get. Are you open to other ideas besides DVWP if still using SPD? –  Rob Wilson Nov 16 '10 at 2:02
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.