Having trouble getting my syntax just right - a little help appreciated. If you scroll to the bottom and note the commented area, I can't seem to get that correct so that it will run.
I've tried a bunch of stuff, the latest being to just create a temp table and store the data in the temp table and then reference that (see just below), but I've got some syntax slightly off.
insert into #myPBL (id, pList)
select rs.id, rs.plist from (select distinct id, stuff((select '~' + code+ ' ' + upper(left(text,1)) + substring(lower(text),2,len(text)-1) from my table with (nolock)
where (did=24) and (id=#C1.id) for xml path('')),1,1,'')plist from #C1 with (nolock))rs;
set @tableHTML = @tableHTML +
N'<table border="1" cellspacing="1" cellpadding="2" style="font-size: 10px;">' + blah blah header
</tr>' +
cast((
select (
td=d2.bp, '',
td=d2.[weight], '',
td=d2.next_appt, ''
for xml path('tr'), type),''
,(select '3' as [td/@colspan],td=comments, '' for xml path('tr'), type)
from #D1 with (nolock) where #D1.imredem=@imredem
--,(select '3' as [td/@colspan],td=#myPBL.pList, '' for xml path('tr'), type)
-- from #myPBL where #myPBL.imredem=@imredem
for xml path('')) AS nvarchar(max)) + '</table>';