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

ahmed elbarbary

5y876

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

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')
RN ValuePortionKey
1 KRM__21X0E100
2 C0AD
3 D
1 KRM__21X0J100
2 K0AD
3 D
1 KRM__21X1C100
2 D0AD
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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
@ahmed, if this is your case then it cannot be done via query, you have to update it manually because as you have 6 rn_uniqueNumber the loop update rn from 1 to 6 with value 1 and in other rows you have only 3, so it updates accordingly. Hope till yet your main query for fetching has been solved. Do let us know.
+1
ahmed elbarbary5y ago
thank you for reply
i try as answer post above but i get wrong result as below
create table #portiontableFinal
(
rn int,
ValuePortionKey nvarchar(100) ,
flaggroupby  int
)
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'),
(4,'L'),
(5,'G')

DECLARE @ctr int = 1, @RN_UniqueRows int = 6, @FlagValue int = 0

WHILE (@ctr <= @RN_UniqueRows)
BEGIN
        SET @FlagValue = 0;
        UPDATE #portiontableFinal SET flaggroupby = @FlagValue,
        @FlagValue = @FlagValue + 1
        where rn= @ctr and flaggroupby is null

        --increment the rn value to get the next result
        set @ctr +=1
END

select * from #portiontableFinal
and i get result as below :
rn  ValuePortionKey flaggroupby
1   KRM__21X0E100   1
2   C0AD 1
3   D 1
1   KRM__21X0J100   2
2   K0AD 2
3   D 2
1   KRM__21X1C100   3
2   D0AD 3
3   D 3
4   L 1 wrong it must be 3
5   G 1 wrong it must be 3
+1
Amit Gupta5y ago
For updating the flagfroupby, you should use loop like below
DECLARE @ctr int = 1, @RN_UniqueRows int = 3, @FlagValue int = 0



WHILE (@ctr <= @RN_UniqueRows)

BEGIN

        SET @FlagValue = 0;

        UPDATE #portiontableFinal SET flaggroupby = @FlagValue,

        @FlagValue = @FlagValue + 1

        where rn= @ctr and flaggroupby is null



        --increment the rn value to get the next result

        set @ctr +=1

END

Output
NOTE: If you have to update 4 or more rows at an incremental value, you can change the @RN_UniqueRows to 4, as we have to update 3 unique rn values, we had use @RM_UniqueRows to 3
+2
ahmed elbarbary5y ago
thank you for reply
can you please tell me how to update
flaggroupby
firstly i need to update flaggroupby
every rn from 1 to 3 take incremental number
update f set f.flaggroupby= from #portiontableFinal f
rn  ValuePortionKey    flaggroupby
1   KRM__21X0E100         1
2   C0AD 1
3   D 1
1   KRM__21X0J100 2
2   K0AD 2
3   D 2
1   KRM__21X1C100         3
2   D0AD 3
3   D 3
+2
Amit Gupta5y ago
@ahmed

try like below
SELECT *,
stuff(
(
    SELECT ''+ ValuePortionKey
    FROM #portiontableFinal
    WHERE flaggroupby = t.flaggroupby
    FOR XML PATH('')
),1,0,'') as ValuePortionKeyData
FROM (SELECT DISTINCT flaggroupby FROM #portiontableFinal ) t
Output
+2
Rajanikant Hawaldar5y ago
try
with CTE_RN as
(
    select *, row_number() over(order by (select null)) as n
    from #portiontableFinal
)

select a.ValuePortionKey + b.ValuePortionKey + c.ValuePortionKey
from CTE_RN as a
inner join CTE_RN as b on a.n + 1 = b.n
inner join CTE_RN as c on a.n + 2 = c.n
where a.n % 3 = 1
+2
ahmed elbarbary5y ago
can you help me please
i will add column flaggroupby
this field will take every group incremental as below
create table #portiontableFinal
(
flaggroupby  int,
rn int,
ValuePortionKey nvarchar(100)
)

update  flaggroupby =max+1

final result
rn  ValuePortionKey   flaggroupby
1   KRM__21X0E100       1
2   C0AD                       1
3   D                            1
1   KRM__21X0J100       2
2   K0AD                       2
3   D                            2
1   KRM__21X1C100      3
2   D0AD                      3
3   D                            3
+2
Amit Gupta5y ago
Before answering, just want to know that how can you relate 3 rows belongs to specific category without any PK column or Category column, so one can use group by category?
+2