sql - How to split String after CONCAT over the repetition of Row Number

Question

I have 2 tables as shown:

I want to CONCAT the two tables by joining them and split them over the repetition of Row Number.

    CREATE TABLE #portiontable (
 PortionKey NVARCHAR(100),
 RN         INT,
 )

CREATE TABLE #finaltable (
 Value     NVARCHAR(100),
 RN         INT,
 )

 INSERT INTO #finaltable (Value,RN)
 VALUES ('KRM__21X0E',1),
        ('C',2),
        ('',3),
        ('',4),
        ('KRM__21X0E',1),
        ('C',2),
        ('',3),
        ('',4)
INSERT INTO #portiontable (PortionKey,RN)
 VALUES ('100',1),
        ('0AD',2),
        ('D',3)
SELECT * FROM #finaltable f
SELECT * FROM #portiontable p

SELECT (SELECT ''+ ValuePortionKey
         FROM (
         SELECT f.RN,f.value,P.PortionKey, f.value + P.PortionKey AS ValuePortionKey
FROM #portiontable p 
INNER JOIN #finaltable f ON p.rn = f.rn
         ) ft
FOR XML PATH('')) as PartSignature


DROP TABLE #portiontable
DROP TABLE #finaltable

The desired output is 2 rows:

PartSignature
KRM__21X0E100C0ADD
KRM__21X0J100K0ADD

The actual output is:

PartSignature
KRM__21X0E100C0ADDKRM__21X0J100K0ADD

question from:https://stackoverflow.com/questions/65898101/how-to-split-string-after-concat-over-the-repetition-of-row-number

Answer 1

Firstly, it seems that you have 2 sets of data in the #finaltable. You need another column to identify it as a set. I have added a ValueSet in the #finaltable.

And, I think your sample data does not correspond to the expected output. I have amended the sample data for #finaltable

And finally, using STRING_AGG to perform the string concatenation, you can then GROUP BY the new ValueSet

CREATE TABLE #portiontable 
(
    PortionKey NVARCHAR(100),
    RN         INT,
)

CREATE TABLE #finaltable 
(
    ValueSet INT,
    Value     NVARCHAR(100),
    RN         INT,
)

 INSERT INTO #portiontable (PortionKey,RN)
 VALUES ('100',1),
        ('0AD',2),
        ('D',3)

 INSERT INTO #finaltable (ValueSet,Value,RN)
 VALUES (1,'KRM__21X0E',1),
        (1,'C',2),
        (1,'',3),
        (1,'',4),
        (2,'KRM__21X0J',1),
        (2,'K',2),
        (2,'',3),
        (2,'',4)


SELECT  f.ValueSet, 
        STRING_AGG (f.Value + p.PortionKey, '') AS ValuePortionKey
FROM    #portiontable p
        INNER JOIN #finaltable f    ON  p.RN    = f.RN
GROUP BY f.ValueSet

DROP TABLE #portiontable
DROP TABLE #finaltable

-- Result
1   KRM__21X0E100C0ADD
2   KRM__21X0J100K0ADD