TSQL: Displaying multiple rows of results as 1 row

Question

All,

Not quite sure how to do the following. Teaching myself SQL, working with SQL Server 2008 R2. Note that while I can perform all the select queries I like, I do not have the permissions to create drop tables in the database.

In my database, there's a table called "messages." Each message is a three letter code (e.g., 'AAA', 'AAB', etc.). Each primary key can have an arbitrary number of messages. So, for purposes of this exercise, say the table looks like this:

1 AAA
1 AAB
1 AAC
2 AAA
2 CCC

etc,

The output I would like to get is to convert this horizontal data to vertical data so I can get this:

1 AAA AAB AAC
2 AAA CCC

If relevant, the database also contains a list of all the possible message codes on a different table.

I suspect the correct answer involves PIVOT, but I am not quite sure how to get there. The closest I found is this: How to pivot table with T-SQL? However, (a) I wasn't sure how to adapt it to my situation and (b) it appears to require creating a table.

Thank you in advance.

You want a single row for the whole table? Or is the formatting just incorrect? — univerio, May 06 '14 at 18:37
@M.Ali: Why did you edit the output? You should leave a comment if you need clarification in expected output shown. (Just like univerio did) — Raging Bull, May 06 '14 at 19:03
@M.Ali: Did you edited output just to look like your answer? — fnightangel, May 06 '14 at 19:04
Go in edit mode and see what OP has tried to show, It will be ridiculous to edit the output only to make it look like my answer. If you read the question properly it is message and One message is divided into three rows. If it makes any sense. — M.Ali, May 06 '14 at 19:06
Goto this [`Link`](http://stackoverflow.com/posts/23502267/revisions) and see yourself how question looked in Edit mode. @fnightangel before you accuse someone of something get your facts right . — M.Ali, May 06 '14 at 19:09
Well, that's what i did in the first place. The problem is that now we have 2 corrected solution. One for before and after update. And sorry if it seems a little strange someone downvoting you just before the edit. Lets wait for @user2561252 to see — fnightangel, May 06 '14 at 19:13

Raging Bull · Accepted Answer · 2014-05-06T19:35:48.330

Since your question has been edited, including both queries:

Query for expected result in Original question:

;WITH CTE AS (
SELECT T2.ID, STUFF(
(SELECT ' '+ T1.Code
FROM TableName T1
WHERE T1.ID = T2.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TableName AS T2
GROUP BY T2.ID)

SELECT TOP 1  STUFF(
(SELECT ' ' + s.Temp
FROM (SELECT CONVERT(varchar(10),ID)+' '+CSV as Temp 
FROM CTE) s
FOR XML PATH('')),1,1,'') AS Result

Result:

RESULT
1 AAA AAB AAC 2 AAA CCC

See result in SQL Fiddle.

Query for expected result in Edited question:

SELECT T2.ID, STUFF(
(SELECT ' '+ T1.Code
FROM TableName T1
WHERE T1.ID = T2.ID
FOR XML PATH('')),1,1,'') AS Codes
FROM TableName AS T2
GROUP BY T2.ID

Result:

ID  CODES
1   AAA AAB AAC
2   AAA CCC

See result in SQL Fiddle.

score 0 · Answer 2 · answered May 06 '14 at 18:45

Test Data

DECLARE @TABLE TABLE(MessageID INT, Body VARCHAR(100))
INSERT INTO @TABLE VALUES
(1, 'AAA'),
(1, 'AAB'),
(1, 'AAC'),
(2, 'AAA'),
(2, 'CCC')

Query

SELECT t.MessageID,
       STUFF((SELECT ' ' + Body
              FROM @TABLE 
              WHERE MessageID = t.MessageID
              FOR XML PATH(''),TYPE)
              .value('.','NVARCHAR(MAX)'),1,1,'')
                    AS FullMessage
FROM @TABLE t
GROUP BY t.MessageID

Result Set

╔═══════════╦═════════════╗
║ MessageID ║ FullMessage ║
╠═══════════╬═════════════╣
║         1 ║ AAA AAB AAC ║
║         2 ║ AAA CCC     ║
╚═══════════╩═════════════╝

TSQL: Displaying multiple rows of results as 1 row

2 Answers2