SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extension of "Totally confused" from yesterday
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 10/03/2012 :  13:47:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  13:57:23  Show Profile  Reply with Quote
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()'
...

Edited by - sunitabeck on 10/03/2012 13:58:29
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 10/03/2012 :  14:14:10  Show Profile  Reply with Quote
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

USA
646 Posts

Posted - 10/03/2012 :  14:21:40  Show Profile  Reply with Quote
Disregard. got it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000