As an extension of this post from yesterday:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179415
Turns out that the process used here did not produced the results required. Why it didn't and how it didn't aren't so relevant. At any rate, the following query is what provides me the resultset i need. However, what i need to do is put this resultset into a new temp table called #NewNarratives. The query is so twisted that i'm having a hard time seeing where to put the "into #NewNarratives" component to create this new temp table. Can anyone discern my goal from this mess??
select DISTINCT CFSID,
( select DISTINCT Descript+char(10)
from (
SELECT DISTINCT CFSID, 'Additional Aggressor IDs in this case are: '+
( select DISTINCT [CTSDescription] +',' --'data()'
from #AggressorIDNarr intbl
where intbl.CFSID = outtbl.CFSID
group by [CTSDescription]
order by [CTSDescription]+','
FOR XML PATH('')) as Descript
FROM #AggressorIDNarr outtbl
--order by CFSID
UNION
SELECT distinct cfsid, 'Additional AbuseTypes Identified: '+
( select DISTINCT [CTSDescription] +',' --'data()'
from #AbuseTypeNarr intbl
where intbl.CFSID = outtbl.CFSID
group by CFSID, [CTSDescription]
order by [CTSDescription]+','
FOR XML PATH('')) as Descript
FROM #AbuseTypeNarr outtbl
) SRCT1
where SRCT1.CFSID = SRCT.CFSID
group by Descript
order by Descript+char(10)
for XML PATH ('') )
from
(
SELECT DISTINCT CFSID, 'Additional Aggressor IDs in this case are: '+
( select DISTINCT [CTSDescription] +',' --'data()'
from #AggressorIDNarr intbl
where intbl.CFSID = outtbl.CFSID
group by CFSID, [CTSDescription]
order by [CTSDescription]+','
FOR XML PATH('')) as Descript
FROM #AggressorIDNarr outtbl
--order by CFSID
UNION
SELECT distinct cfsid, 'Additional AbuseTypes Identified: '+
( select DISTINCT [CTSDescription] +',' --'data()'
from #AbuseTypeNarr intbl
where intbl.CFSID = outtbl.CFSID
group by CFSID, [CTSDescription]
order by [CTSDescription]+','
FOR XML PATH('')) as Descript
FROM #AbuseTypeNarr outtbl
) SRCT
Extension of "Totally confused" from yesterday
Posted - 10/03/2012 : 13:47:51
