coalesce from Tmptable - SQL Server Forums

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

coalesce from Tmptable

Author

Topic

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-20 : 17:13:12

How can I coalesce data from a TmpTable.

Because of our database structure, I need to run separate quries using joins to return the final results. I need to take those final results and coalesce one of the columns based on another column.

Example of ##TmpTable
RecordID CompID
12 15
12 16
12 17
13 18
14 19
14 20

Returned results:
RecordID CompID
12 15,16,17
13 18
14 19,20

I was given assistance here before on using a function to perform the coalesce and it works like a dream, however, the function will not work when using a ##TmpDatabase.

If needed, I can place the script that I am using to create the ##TmpTable, but because of the complexity, there are 4 actual tables used, using joins, to create my one ##TmpTable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 17:24:29

If you are using SQL Server 2005, try the CROSS APPLY approach instead.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-21 : 10:25:53

cross apply doesn't really work for what I'm trying to do. Here is the script to get the info into my ##TmpTable:

select * into ##TmpReport from stringvalues where FK_FieldID = '1416'
select * into ##TmpCompJobID from stringvalues where FK_FieldID = '2970'
select * into ##TmpLandID from integervalues where FK_FieldID = '2958'
select * into ##TmpCompID from stringvalues where FK_FieldID = '898'
select a.FK_ReferenceID as CompLinkID,a.value as JobID,b.value as LandID,c.FK_ReferenceID as RecordID,d.FK_referenceID as CompID from ##TmpCompJobID a
inner join ##TmpLandID b on a.FK_ReferenceID = b.FK_ReferenceID
inner join ##TmpReport c on a.value = c.value
inner join ##TmpCompID d on d.value = b.value
drop table ##TmpReport
drop table ##TmpCompJobID
drop table ##TmpLandID
drop table ##TmpCompID

The CompID field will be the unique values, based on the RecordID, and it is the CompID field I need to have returned as a comma separated value. (The other columns are brought in as they are used to ultimately sych everything.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources