June 16, 2010 at 9:11 am
Stefan_G (6/16/2010)
But what should happen if the table contains more than one UserID ?
I want the same thing to happen for every userid.
I want all rows of memos for each userid compressed into one row for each userid instead of many rows for each userid
June 16, 2010 at 9:25 am
OK, now I think I understand what you want.
Try this:
select
userid,
substring((
select ' : ' + cast(memo as varchar(max)) + ' , ' + cast(memodate as varchar(max))
from Notes n2
where n2.userid = n1.userid
order by MemoDate
for xml path(''),type
).value('.[1]','varchar(max)'), 4, 10000000) as All_Notes
from Notes n1
group by userid
order by userid
June 16, 2010 at 9:40 am
Stefan_G (6/16/2010)
OK, now I think I understand what you want.Try this:
selectuserid,
substring((
select ' : ' + cast(memo as varchar(max)) + ' , ' + cast(memodate as varchar(max))
from Notes n2
where n2.userid = n1.userid
order by MemoDate
for xml path(''),type
).value('.[1]','varchar(max)'), 4, 10000000) as All_Notes
from Notes n1
group by userid
order by userid
YES! that worked correctly.. now I'm off to read more about using substring and xml path
Thanks
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply