Concatenating Row Values in T-SQL

Assuming you have a table like

temptable

and you want to print out a row containing all the players and games for a given gameId like

result

Then you can use query

DECLARE @temp TABLE (GameId INT, PlayerName CHAR(8), Score INT)

INSERT INTO @temp ([GameId],[PlayerName],Score) VALUES (1,'John',91)
INSERT INTO @temp ([GameId],[PlayerName],Score) VALUES (1,'Mary',100)
INSERT INTO @temp ([GameId],[PlayerName],Score) VALUES (2,'Sam',98)

select * from @temp

SELECT
  GameId,
  STUFF((
    SELECT ', ' + PlayerName + ':' + CAST(Score AS VARCHAR(8))
    FROM @temp
    WHERE (GameId = Results.GameId)
	--instead of returning rowset, let's return xml
	--try to set path to a non-empty tag to see the xml
    FOR XML PATH(''))
  --delete the first , and space
  ,1,2,'') AS NameScorePairs
FROM @temp Results
GROUP BY GameId				
Advertisements
This entry was posted in Database and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s