How to select into table as string builder instead of using cursor in SQL

Question

I am wondering if there is a faster way to handle the following code in SQL. Currently I am using SQL cursor to do a select and build a string of delimiter values, as a dynamic value of suggestion items?

Here is the snippet of SQL:

    begin
      set @cursor = cursor for 
      select top 5 Manufacturer,ManufacturerPartNumber,Description as ManufacturerDescription, CONVERT(money,Price) as Price,fms.Score  
      from Products_OurProducts_Products_View

      open @cursor 
      fetch next from @cursor 
      into @CURSOR_Mfr,@CURSOR_Model,@CURSOR_Desc,@CURSOR_Price,@CURSOR_Score

      while @@FETCH_STATUS = 0
        begin
          set @suggestionsStringBuilder += @CURSOR_Mfr + ',' + @CURSOR_Model + ',' + @CURSOR_Desc + ',' + convert(varchar(20),@CURSOR_Price) + ',' + convert(varchar(4),@CURSOR_Score) + '^'
          fetch next from @SuggestionsListCursor 

          into @CURSOR_Mfr,@CURSOR_Model,@CURSOR_Desc,@CURSOR_Price,@CURSOR_Score
        end

    insert into BASE (Manufacturer, ManufacturerOrig, ManufacturerPartNumber,ManufacturerPArtNumberOrig,ManufacturerDescription, QWDescription, Serial,AssetID,Price,Score,ItemType,MfrFound,ModelFound,trained, SuggestionList,LineNumberIn)
    values(@objectORIGMfr,@objectORIGMfr, @objectORIGModel, @objectORIGModel, @objectDescription, @objectDescription, '',@objectAssetID,'0.00',@topMaxScore,'NA','1','0',@trained,@suggestionsStringBuilder,@objectLineNumber)

    close @cursor
    deallocate @cursor
end

The code above is trying to build a dynamic column of delimiter values such as shown below:

Object Example:

Mfr,
Model,
Price,
Score,
Description,
Suggestions = 'Mfr,Model,Desc,Price^Mfr,Model,Description,Price^

A return model would truly be as follows:

BaseMfr:Fluke,
BaseModel:Tb1,
BaseDescription:'Multi meter item',
BasePrice:120.00,
Suggestions: "Fluke, Tc1, 'Desc', '120.00' ^ 'Fluke', 'T11', 'Desc', 220.00"

Can I do the string builder / cursor section without having to use a looping cursor? The idea behind this is we send in items to be priced. If the item is not found, we then build a list of suggestions to bring back to the user of what they may use in the system or so they can see if there is a typo in the data.

The suggestion list is just the rows found, separating the columns by a "," and separating entities by a "^".

Thanks very much in advance!

When you want to do a dynamic pivot you use XML Path to concatenate the data into csv https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query but without some sample data I wont try to replicate it. — Juan Carlos Oropeza, Oct 02 '19 at 17:54
Thanks for the advice on How-To-Ask but I figured this was enough to get a response. I will update with the expected output data model. — Casey ScriptFu Pharr, Oct 02 '19 at 18:42
Post the tables and data as text [READ THIS](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Juan Carlos Oropeza, Oct 02 '19 at 19:16
If you post db schema, sample data, current and expected output. I will take a look again. Otherwise, I won't waste my time trying to understand what you need. — Juan Carlos Oropeza, Oct 02 '19 at 19:19
Depending on the version you are using either STRING_AGG or FOR XML should be able to handle this easily. — Sean Lange, Oct 02 '19 at 20:29
Perfect that is what i was wondering. I do not know a good way to explain my issue without all the code and diagrams ect, and is too much information. To translate a cursor into STRING_AGG or XML? I was also thinking if it were possible to define the column as a sub query and string build 'COL + ',' + COL2 +'^' etc. — Casey ScriptFu Pharr, Oct 02 '19 at 22:44
No way, thanks @Sean Lange. I found a way so now to test the speed performance on it. — Casey ScriptFu Pharr, Oct 02 '19 at 22:59

score 0 · Answer 1 · answered Oct 02 '19 at 23:02

Thanks you all for the feedback and I appreciate the help even though I know I had a rough time explaining the question correctly. Thanks to the suggestion from Sean Lange, I was able to be directed in the correct direction and came up with this. Now I will test the performance of it to see if it is better or not. Here is the code:

select 
    SUBSTRING(
        (select top 5 
            Manufacturer + ',' + ManufacturerPartNumber + ',' + Description +',' + CONVERT(VARCHAR,Price) +',' + CONVERT(varchar,fms.Score) +'^' as [text()] 
        from Products_OurProducts_Products_View
        CROSS APPLY (
            select 
                dbo.FuzzyControlMatch('Flooke', Manufacturer) AS score
            ) AS fms
        order by fms.score desc
        FOR XML PATH ('')
        ), 2, 1000) [Suggestions]

The above code produces the following string:

ARD BROOKE,WB808 10UNF,TORQUE SCREWDRIVER,70.00,50^WARD BROOKE,WB808 1146,TORQUE SCREWDRIVER,70.00,50^WARD BROOKE,WB808 1246,TORQUE SCREWDRIVER,70.00,50^WARD BROOKE,WB808 6UNC,TORQUE SCREWDRIVER,70.00,50^ROKEM TECHNOLOGIES,FIRESET,RC STANDARD,105.00,50^

Now I am not sure if I am handling this the best way, but this is what I was searching for. I will post a comment update to let the feed know if the performance is better or worse.

-Thanks-

One issue I have, is how do i set a variable VARCHAR(MAX) fro the query above? Seems to cause errors when I try it. — Casey ScriptFu Pharr, Oct 02 '19 at 23:29
This is a lot slower then using the prior method of cursors. — Casey ScriptFu Pharr, Oct 03 '19 at 10:59
The performance issue is almost certainly due to that scalar function you have in there. — Sean Lange, Oct 03 '19 at 12:37

How to select into table as string builder instead of using cursor in SQL

1 Answers1