sql-serverindexingstring-concatenationcursors

Concat rows from the same field, according to coincidence with other table in SQL?

I have a problem trying to upload the field caracter from a table t1. I want to concat the value from caracter if this appears in frase from t2, with the previous value. For example if a found x, y or z in caracter and these values appear in frase from t2, I'd like to concat x, y and z with the previous value in caracter.

t1

enter image description here

t2

enter image description here

This should be the result

t1

enter image description here

I tried with a cursor but it doesn't work because I have a mistake referencing at set, previously I tried with while loop but had the same result.

 DECLARE @frase VARCHAR (100)
 DECLARE MICURSOR CURSOR FOR SELECT caracter FROM t1 
 OPEN MICURSOR
 FETCH NEXT FROM MICURSOR 

 WHILE @@fetch_status = 0

 BEGIN

 SELECT t1.caracter FROM t1 INNER JOIN t2 
 ON t2.frase LIKE CONCAT('%', t1.caracter,'%')
 UPDATE t1 SET caracter=  'caracter' + '(@frase-1)'

  FETCH NEXT FROM MICURSOR INTO @frase
END
CLOSE MICURSOR
DEALLOCATE MICURSOR
Solution

  • Hope this serves the purpose,You have to utilize lead() aggregate function to fetch next row. You can find complete solution in dbfiddle link with table and records populated to demonstrate how the query works. The solution is for SQLSEVER 2019. https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=71510d727bb04478383887e2e8ab3a52

    update t1 set t1.caracter = concat(t1.caracter,lg)
from
 (select id_val,ver,caracter,lead(caracter) over (order by id_val) lg from t1) tab,t1
 where lg in (select frase from t2)
 and tab.caracter = t1.caracter;

 delete from t1 where caracter in (select frase from t2);

select * from t1;

id_val  ver caracter
1   abc abc
1   abc 3
1   abc x
1   abc 2
1   abc y
2   def def
2   def 5
2   def y
2   def 9
2   def z

    The output after script is applied,

    id_val  ver caracter
1   abc abc
1   abc 3x
1   abc 2y
2   def def
2   def 5y
2   def 9z