CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user2561252
user2561252

Reputation: 47

TSQL: Displaying multiple rows of results as 1 row

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.

Upvotes: 3

Views: 315

Answers (2)

Raging Bull
Raging Bull

Reputation: 18747

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.

Upvotes: 3

M.Ali
M.Ali

Reputation: 69524

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     ║
╚═══════════╩═════════════╝

Upvotes: 0

Related Questions