Consolidating Data From Multiple Rows Into A Single Varchar(max) Field, Is There An Alternative To A Cursor And .write

September 08, 2024 Post a Comment

I have some data in a source table that looks like the below. This is basically a narrative field that has been split into 47 character chunks. There is a convention in the data

Solution 1:

Try this out for SQL Server 2014.

Createtable #narrdata (parentindex int, linenum int, linetext nvarchar(max))

    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 1, 'This is a narrative which should be separated')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 2, 'into several lines in the source data.  I have')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 3, 'no idea which line this part will be in.  This')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 4, 'however should be before a newline.`')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 5, 'A new line just started here.`')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 6, 'Another new line here.  Then some blank lines.`')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 7, '`')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 8, '`')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 9, 'How about this now?  Is this enough sample')
    INSERTINTO #narrdata (parentindex, linenum, linetext) VALUES (37791, 10, 'data.')

SELECT STUFF((SELECT' '+ inr.linetext
                FROM #narrdata inr
               WHERE inr.parentindex = nr.parentindex
               ORDERBY inr.linenum
                 FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
  FROM #narrdata nr
 GROUPBY nr.parentindex

Solution 2:

This should do the trick for you

INSERTINTO narrdata2 (originalparentindex, linetext) 

SELECT parentindex, STUFF(
                        (

SELECT' '+CASEWHEN z.linetext ='`'THENNULLELSE z.linetext ENDFROM narrdata z
WHERE z.parentindex = y.parentindex
ORDERBY z.linenum
FOR xml path('')
)
                        , 1
                        , 1
                        , '') FROM narrdata y GROUPBY parentindex

I've called the new table narrdata2 with the following structure, hope it helps!

CREATETABLE narrdata2 (parentindex INT, originalparentindex INT, linetext VARCHAR(MAX))

Solution 3:

Based on the previous answers, this seems to work. I switched the handling of added spacing to the end. And I removed the seemingly extraneous STUFF call which was just removing the first character, I think. This leaves the last line with an extra space character but that's okay. I added in the REPLACE call to replace the tick marks with CRLF. The , TYPE.value thing doesn't seem to be required as when I tested with lengths exceeding 8000 it seemed to work. I should probably look up the max length of the xml data type. There seems to be an implicit type conversion happening here with or without the REPLACE call. I'd like to understand that more.

I've used FOR XML PATH before for something similar, though with actual XML, I just didn't consider it. I'm open to other answers that don't involve FOR XML.

SELECT 
   parentindex,                  
    REPLACE( (SELECT z.linetext +CASEWHENRIGHT(z.linetext, 1) ='`'THEN''ELSE' 'ENDFROM #narrdata z WHERE z.parentindex = y.parentindex ORDERBY z.linenum FOR XML PATH(''), TYPE).value('.', 'VARCHAR(max)'), '`', CHAR(13) +CHAR(10))
FROM #narrdata y
GROUPBY parentindex

Solution 4:

In your request for additional answers not using XML, please see the below using T-SQL loops.

Not sure if it is more efficient but it is certainly more fun. :)

CREATETABLE #target_table
(
    myindex INT, 
    narrative VARCHAR(MAX)
)

DECLARE@outputVARCHAR(MAX)
DECLARE@loopINT=1DECLARE@lineloopINT=0DECLARE@maxloopINT-- find the max parent indexSET@maxloop= (SELECTMAX(maxloop.parentindex) FROM narrdata maxloop)

DECLARE@maxlineloopINT-- loop through the parent indexes
WHILE @loop<=@maxloopBEGIN-- find the max linenum for that the current parent indexSET@maxlineloop= (SELECTMAX(lineloop.linenum) FROM narrdata lineloop WHERE lineloop.parentindex =@loop)

    -- loop through the linenums for the current parent index
    WHILE @lineloop<=@maxlineloopBEGINSET@output= (SELECT ISNULL(@output+' ','') + z.linetext 
        FROM narrdata z
        WHERE z.parentindex =@loopAND z.linenum =@lineloop)
        --Strip out the line breaks chars (plus the additional space before it)SET@output= REPLACE(@output,' `','')
        -- add one to the linenumSET@lineloop+=1END-- INSERT THE row into the temp tableINSERTINTO #target_table (myindex, narrative) VALUES (@loop, @output)
    -- Set the linenum back to 0SET@lineloop=0--add 1 to the parentindex loopSET@loop+=1END-- Present the resultsSELECT*FROM #target_table

-- Drop the temp tableDROPTABLE #target_table

Solution 5:

I found another technique but this seems to work only on one index at a time. Is there a way to adapt this to work on the whole table, aside from the obvious loop and cursor?

DECLARE@narrativevarchar(max)
SELECT@narrative=COALESCE(@narrative+CASEWHENRIGHT(@narrative, 1) ='`'THEN''ELSE' 'END, '') + linetext
FROM #narrdata
WHERE parentindex =37791ORDERBY linenum

SELECT REPLACE( @narrative, '`', CHAR(13) +CHAR(10) )

summer2012training