SQL query that needs to join comma seperated values to another table

Question

Post reply

SQL query that needs to join comma seperated values to another table

jjf410

SSC Enthusiast

Points: 128
More actions
June 1, 2010 at 2:55 pm

#222146

I need to run a query on a sql 2000 server the query needs to join two tables
The first table has ID and Dealid( the dealid are stored as csv) the second table has dealid and dealValue.
I need to joing each of the csv dealid with one dealid in the second table to get the deal value.
First table
iddealid
56171926, 193441
56171926, 193441
761173,009,173,698
827171,981,168,743
1019172906,191117, 232835, 287740
432171941, 171942, 241262
1041173,276,176,618,194,000
1043169683, 169687
552179,269,191,746
1159193,691,178,378
Second table
DealidDealValue
17354,242.00
743554,456.00
28774036,589.00
2328352,457.00
172906145,385.00
do I need to make another table and split the dealid?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You don't need to.

But it certainly helps performance... 😀

There are numerous split string functions available. Somer perform better than others...

I'm not sure if you're running SQL2000 (I assume you do based on the forum you posted in). In that case I recommend you search this site for a SQL2000 split string function. But if you're running SS2K5 or above you should specifically search for [dbo].[DelimitedSplit8K] (it should point you at an excellent performing function written by Jeff Moden).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jjf410 SSC Enthusiast Points: 128 More actions · Answer 2

I need to get the query to do the join when executing so the result set would a row for each deal value with the id repeating

ID Dealvalue

1 120.00

1 135.00

1 115.00

2 200.00

2 123.00

I know how to do the string split but I'm looking for the best way to do the join,

LutzM SSC Guru Points: 107049 More actions · Answer 3

It depends.

Which ID do you want as a result? The one from FirstTable or the one from the table generated by the split function? How do you plan to deal with duplicate Dealvalues within one ID? MIN/MAX/AVG/SUM?

To get the issue resolved please post table defs for first and second table including sample data and your expected result based on your sample data. Also, please post your split string function and what you've tried so far. Data provided should be in a ready to use format as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jjf410 SSC Enthusiast Points: 128 More actions · Answer 4

My requirements changed and I was able to use a Function to sum the deal totals. Thanks for the help.