Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extension of "Totally confused" from yesterday

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-03 : 13:47:51
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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 13:57:23
Just before the outermost FROM clause
...
GROUP BY
Descript
ORDER BY
Descript + CHAR(10)
FOR XML PATH('')
) AS SomeColName
INTO #NewNarratives

FROM (
SELECT DISTINCT CFSID,
'Additional Aggressor IDs in this case are: ' +(
SELECT DISTINCT [CTSDescription] + ',' --'data()'
...
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-03 : 14:14:10
Thank YOU.

Lastly, i have a function that i need to create in this process, but i want to drop it if exists first. What would change in the same "drop if exists" statement that i would use for a table in the case of a function?



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CTSOFFENSEMAPtbl]') AND type in (N'U'))
DROP TABLE [dbo].[CTSOFFENSEMAPtbl]

Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-03 : 14:21:40
Disregard. got it.
Go to Top of Page
   

- Advertisement -