How to get value portion key to every rn from 1 to 3 row on one row?

See more:

SQL

stored-procedure

I need to get value portion key to every 3 row on one row meaning

rn 1,2,3 one row KRM__21X0E100C0ADD
rn 1,2,3 one row KRM__21X0J100K0ADD
rn 1,2,3 on one row KRM__21X1C100D0ADD

SELECT P.GlobalPnId,P.PortionKey,f.*, f.value + P.PortionKey AS ValuePortionKey
FROM #portiontable p 
INNER JOIN #finaltable f ON p.rn = f.rn

GlobalPnId	PortionKey	value	RN	ValuePortionKey
37599	100	KRM__21X0E	1	KRM__21X0E100
37599	0AD	C	2	C0AD
37599	D		3	D
37599	100	KRM__21X0J	1	KRM__21X0J100
37599	0AD	K	2	K0AD
37599	D		3	D
37599	100	KRM__21X1C	1	KRM__21X1C100
37599	0AD	D	2	D0AD
37599	D		3	D

I need to get from 1 to 3 on one rows as following :

final result I need to get as below :
KRM__21X0E100C0ADD
KRM__21X0J100K0ADD
KRM__21X1C100D0ADD



exactly i need one row above display on 3 parts as

Final Result :

ValuePortionKey
KRM__21X0E100C0ADD
KRM__21X0J100K0ADD
KRM__21X1C100D0ADD

What I have tried:

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

but i get wrong result because result display all on one row :slight_smile:

PartSignature
KRM__21X0E100C0ADDKRM__21X0J100K0ADDKRM__21X1C100D0ADD