September 21, 2011 at 1:18 pm
First, I'll provide some test data:
DECLARE @TestData TABLE (
RowID int,
GroupID int,
Data nvarchar(500)
)
insert into @TestData (RowID, GroupID, Data)
select 1, 1, 'Test Data 1'
UNION
select 2, 1, 'Test Data 2'
UNION
select 3, 1, 'Test Data 3'
UNION
select 4, 1, 'Test Data 4'
UNION
select 5, 2, 'Test Data 5'
UNION
select 6, 2, 'Test Data 6'
UNION
select 7, 3, 'Test Data 7'
UNION
select 8, 3, 'Test Data 8'
UNION
select 9, 3, 'Test Data 9'
UNION
select 10, 3, 'Test Data 10'
UNION
select 11, 3, 'Test Data 11'
UNION
select 12, 4, 'Test Data 12'
This is what I want my output to look like:
DECLARE @Output table (
RowID int, GroupID int, Data nvarchar(max)
)
INSERT INTO @Output (RowID, GroupID,Data)
SELECT 1, 1, 'Test Data 1 Test Data 2 Test Data 3'
UNION
select 2, 2, 'Test Data 4 Test Data 5'
UNION
select 3, 3, 'Test Data 6 Test Data 7 Test Data 8 Test Data 9 Test Data 10 Test Data 11'
UNION
select 4, 4, 'Test Data 12'
select * from @Output
I'm coming up against a writer's block here with this. I've tried FOR XML, but I keep getting all of the data in every output row. I need a way to break apart which data goes in the Output row based on Group Number.
My ultimate goal will be to run this type of query across a recordset with multi-millions of rows, so scalability is a must. 🙂 Any suggestions?
Thanks for the help!!
September 21, 2011 at 2:52 pm
I am not sure how scalable this will be but it does do what you want it to for the sample data.
SELECT
t1.GroupID,
MemberList = substring((SELECT ( ' ' + data )
FROM @TestData t2
WHERE t1.GroupID = t2.GroupID
ORDER BY
GroupID,
data
FOR XML PATH( '' )
), 2, 1000 )FROM @TestData t1
GROUP BY GroupID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply