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

Transact-SQL
,
1

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 :slight_smile:

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

what i try is

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

exactly i need one row above display on 3 parts as

Final Result :

ValuePortionKey
KRM__21X0E100C0ADD
KRM__21X0J100K0ADD
KRM__21X1C100D0ADD
3

sorry
sample data
create table #portiontableFinal
(
rn int,
ValuePortionKey nvarchar(100)
)
insert into #portiontableFinal(rn,ValuePortionKey)
values
(1,'KRM__21X0E100'),
(2,'C0AD'),
(3,'D'),
(1,'KRM__21X0J100'),
(2,'K0AD'),
(3,'D'),
(1,'KRM__21X1C100'),
(2,'D0AD'),
(3,'D')

Final Result :

ValuePortionKey
KRM__21X0E100C0ADD
KRM__21X0J100K0ADD
KRM__21X1C100D0ADD
5

see i solve my issue
but i need to change (select ((id - 1) / 3) + 1
to be dynamic
so can any one help me

 CREATE TABLE #MYTABLE 
  (
  GlobalPnId INT,
  PortionKey VARCHAR(10),
  value VARCHAR(20),
  RN INT,
  ValuePortionKey VARCHAR(100)
  )
    
  INSERT INTO #MYTABLE VALUES
 (37599,'100','KRM__21X0E',1,'KRM__21X0E100'),
 (37599,'0AD','C',2,'C0AD'),
 (37599,'D',NULL,3,'D'),
 (37599,'100','KRM__21X0J',1,'KRM__21X0J100'),
 (37599,'0AD','K',2,'K0AD'),
 (37599,'D',NULL,3,'D'),
 (37599,'100','KRM__21X1C',1,'KRM__21X1C100'),
 (37599,'0AD','D',2,'D0AD'),
 (37599,'D',NULL,3,'D')

 select * from #MYTABLE
    
 ;with cte as (
 SELECT *,ROW_NUMBER() over (partition by GlobalPnId order by (select null)) id FROM #MYTABLE)
 ,cte1 as (select ((id - 1) / 3) + 1 AS Group_Id,* from cte)
 select replace(STUFF((SELECT ' '+ValuePortionKey from cte1  where Group_Id=a.Group_Id for XML PATH('')),1,1,''),' ','') AS ValuePortionKey
 from cte1 a
 group by a.Group_Id
6

thank you for reply

some rn from 1 to 3 or from 1 to 4 OR FROM 1 TO 5 so max number not known

meaning I need to use any dynamic number in place of 3

so how to make 3 dynamic number

this answer is correct for max group 3

but i have more case exceed 3

so how to solve it

7

There has to be another key the groups these records together, otherwise you're just hardcoding and guessing. Is there a date that groups them together or some other field?