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

Question

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

Amit Gupta · Answer

For updating the flagfroupby, you should use loop like below DECLARE @ctr int = 1, @RN_UniqueRows int = 3, @FlagValue int = 0 WHILE (@ctr 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

Amit Gupta · Answer

@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

ahmed elbarbary · Answer

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

Rajanikant Hawaldar · Answer

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

ahmed elbarbary · Answer

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

Amit Gupta · Answer

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?

Amit Gupta · Answer

@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.

ahmed elbarbary · Answer

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