November 16, 2011 at 9:59 am
Hello Everyone
I am in need of a way to concatenate values that have matching ID's and SubID's and place them into a single row. If the SetID and the SubSetID are the same, they need to be concatenated into a single row, if not, then insert this into a row of it own. Any time either the SetID or SubSetID changes, then start a new row.
IF OBJECT_ID('TempDB..#MyTempTable','U') IS NOT NULL
DROP TABLE #MyTempTable
create table #MyTempTable
(
SetID int
, SubSetID int
, TextMessage varchar(100)
, PRIMARY KEY CLUSTERED (SetID, SubSetID)
)
INSERT INTO #MyTempTable
(
SetID
, SubSetID
, TextMessage
)
SELECT 15,1,'The Fox and the Hounds'
UNION ALL SELECT 16,1,'The Sly Brown Fox' -- concatenate with the one below
UNION ALL SELECT 16,1,'Jumps over the fence'
UNION ALL SELECT 16,2,'The Hunters are on the lookout'
UNION ALL SELECT 17,1,'For the sly brown fox'
UNION ALL SELECT 17,2,'The Hunters are all dressed' -- concatenate with the one below
UNION ALL SELECT 17,2,'In their red and black' -- concatenate with the one below
UNION ALL SELECT 17,2,'Riding on big horses' -- concatenate with the one below
UNION ALL SELECT 18,1,'With a large pack of hounds'
UNION ALL SELECT 18,1,'Leading the way thru the woods'
UNION ALL SELECT 18,1,'Hot on the trail of the fox'
UNION ALL SELECT 18,2,'After a long day, the fox escapes to his foxhole'
UNION ALL SELECT 18,2,'and the hunters return home empty handed'
The results needs to look like this. I have been using a couple of Table Variables to insert the data.
SetID SubSetID TextMessage
15 1 The Fox and the Hounds
16 1 The Sly Brown Fox Jumps over the fence
16 2 The Hunters are on the lookout
17 1 For the sly brown fox
17 2 The Hunters are all dressed In their red and black Riding on big horses
etc......
Thank You in advance for any and all help or suggestions
Andrew SQLDBA
November 16, 2011 at 10:08 am
SELECT SetID, SubSetID, STUFF((SELECT ' ' + TextMessage
FROM #MyTempTable r2
WHERE r2.SetID = r1.SetID AND r2.SubSetID = r1.SubSetID
ORDER BY SetID, SubSetID
FOR XML PATH('')), 1, 1, '') AS TextMessage
FROM #MyTempTable r1
GROUP BY SetID, SubSetID
November 16, 2011 at 10:38 am
Very nice, thank you.
I appreciate that, I too am just trying to learn.
Andrew SQLDBA
November 16, 2011 at 1:17 pm
Your data doesn't have enough information to fully specify the order, so the order is not completely guaranteed. For example 17 could easily render as "Riding on big horses In their red and black The Hunters are all dressed."
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply