How can I combine multiple rows into one 1

[MsChelle]

I need to create a flat file for import into a 3rd-party application. The format of the data versus the table structure of the source is driving me crazy.

Table structure example:

Site BR Tx
ABC 11 8526750
ABC 12 8526850
ABC 12 8526855
ABC 13 8526950
DEF 11 8526850
DEF 12 8526750
DEF 13 8526855


The file I need to generate would look like:

Site TxList
ABC 8526750;8526850;8526855;8526950
DEF 8526850;8526750;8526855


It has to be in that format (semi-colon seperated list in one field, one line per site). There are actually more fields required, but I've got everything else covered except this part.

There can be anywhere from 2-20 rows of data for each site in the source data. There are 2-3 thousand sites.

I am using SQL Server 2000.

 

[AlexCuse]

http://www.tek-tips.com/faqs.cfm?fid=6466

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Take a look here: thread183-1159740

If you have any other questions, please feel free to post back here (in this thread).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MsChelle]

AlexCuse - The examples on the FAQ are not practical for my issue because (in the first one) I would need 20 joins, on top of the others I need for the other fields. And in the second one, I don't have a table with any existing value to step through with the counter.

gmmastros - The example on this thread works, but requires me to specify the site each time. Would I have to create a temp table with all the other fields and unique site names, then loop that over the @result query and update the temp table with each iteration....or is there a way to incorporate this into another query?

I've tried adding the other fields (including site) to the select @result = ... query, but it gives me an error. Trying to incorporate it as a subquery gives me a problem with how do I join.

If I'm stuck looping, se la vie, but if you have a suggestion to avoid that, I'm all ears.

Thanks to both of you!

 

[gmmastros]

Take a closer look at the post dated 2 Dec 05 11:16. In this post, I show how to create a user defined function that returns a comma delimited string. I also show sample syntax on how to call the function within a larger query.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

First, create this user defined function:

Create Function GetTxList
  (@Site VarChar(100))
Returns VarChar(8000)
As
Begin
Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ';', '') + TX
From   [!]TableName[/!]
Where  TX Is Not NULL
       And Site = @Site
Order By TX

Return @Result
End

Then, call it like this...

Select Distinct Site, dbo.GetTxList(Site) As TxList
From   [!]TableName[/!]
Order By Site

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MsChelle]

I'm running it now. It's been running for 15 minutes just for the site and txlist.

 

[MsChelle]

Hah! It finished 30 seconds after I posted this! It's a long run, but it only runs once a day and I can time it when there aren't many users on the system (right now is peak traffic).

Thanks so much for your help!!

 

[gmmastros]

>> There can be anywhere from 2-20 rows of data for each site in the source data. There are 2-3 thousand sites.

With that amount of data, this shouldn't be taking so long. You may need to add an index to your table.

In the function, I had...
[tt][blue]
Select @Result = IsNull(@Result + ';', '') + TX
From TableName
Where TX Is Not NULL
And Site = @Site
Order By TX
[/blue][/tt]

You may have needed to modify this function to suit your needs, and that's ok. I suggest you run this query outside of the function and look at the execution plan. So...

Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ';', '') + TX
From   TableName
Where  TX Is Not NULL
       And Site = '[!]abc[/!]'
Order By TX

Before running this, press CTRL-K on your keyboard (assuming you are using Query Analyzer to run this). After pressing CTRL-K, then run the query. You will notice an "execution plan" tab at the bottom of the window. Look through the execution plan for 'table scans'. If you see any table scans, then you could add an index to the table to speed up the query.

Good luck.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MsChelle]

The first run, I had a table scan. So I fixed the indexes, and now most of the cost is on the sort.

I reran the site, txlist query and it's still taking about 15 minutes. I have 79% cost in the Hash Match/Aggregate. (20% on Index Scan).

??

 

[gmmastros]

Notice that the query is using the Site and the TX column (TX for the order by). Try creating an index that has both column in it.

Also... Does this table have a clustered index. Can there be duplicates in the data. I mean, can the combination of site and tx be duplicate? If not, and you don't already have a clustered index on the table, then make a clustered index that includes both columns. I think this will improve your performance. Predicting what indexes should be is NOT FUN. By playing around a little, you may stumble on an index that gives you really good performance. Just make sure you drop any indexes that don't improve the performance because indexes will slow down other operations (like insert, updates, and deletes).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MsChelle]

I created 2 indexes - one with site and one with tx. (I have other queries where these seperate indexes would help).

I'll throw in the tx on the site one and cluster it and see what that does.

Inserts are only once a day (truncates followed by bulk inserts), during non-peak, so slowing it a bit isn't a problem.

Thanks again!!

 

[MsChelle]

Hm. That still took 15 minutes. I was wondering if the problem is that I have to concatenate the site field from 2 others (these are indexed in the clustered index). I am building a temp table to pull the concatenated site info and running the function on those results.

Doing it this way took a minute and a half.

(Suppose I should have mentioned the concatenated fields, but didn't think it would make THAT big a difference!...live and learn).

Thanks again and again for your help! I think I'm good from here!

 

[gmmastros]

I'm glad you got it working. And I suppose we could stop here, because you are satisfied with the results. However... you do know that you can create indexes on temp tables, right?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MsChelle]

I'm not sure what you're asking so, aside from table design or using create index in QA, no?

 

[gmmastros]

Can you show the code where you are creating the temp table?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MsChelle]

Here's the whole thing (minus the function):

[ drop table #tmpactix
create table #tmpactix (sitesect varchar(30))
create index #tmpindx1 on #tmpactix (sitesect)
insert into #tmpactix (sitesect) (select distinct site + '-' + cast(sector as char(1)) from carrier)

select distinct #tmpactix.sitesect, dbo.GetTxList(#tmpactix.sitesect)
from #tmpactix

drop table #tmpactix /]

 

[MsChelle]

the drop table at the top is residual...scratch that part.

 

[gmmastros]

I must have misunderstood. Sorry.

I see that you are selecting distinct values in to the temp table, so you don't need distinct the the final select.

Also, you may get slightly better performance using a table variable instead. Something like...

Declare @tmpactix Table (sitesect varchar(30) Primary Key Clustered)

insert into @tmpactix (sitesect) (select distinct site + '-' + cast(sector as char(1)) from carrier)

select sitesect, dbo.GetTxList(sitesect)
from @tmpactix

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MsChelle]

It actually took longer that way, but thanks for the tip. I didn't know you could do it that way.

I've got to quit for the day.

Thanks again for all your help!!