CODEWITHSUNDEEP
sqlsql-serverwindow-functionsrow-number
madmonk88
madmonk88

Reputation: 179

SQL partitioning rows with row_number by a columns value

Need help to partition a data result to get row numbers by a resultant column value.
The result is from multiple views but will explain the problem statement with small table below.

create table example ( seqid bigint, itemid bigint , itemname varchar(50), itemdescription varchar(50), subitemname varchar(50), columncount bigint ) 

insert into example
select 1,1,'item1','itemdescription1','sub1',3 union all
select 2,1,'item1','itemdescription1','sub2',3 union all
select 3,1,'item1','itemdescription1','sub3',3 union all
select 4,1,'item1','itemdescription1','sub2.1',3 union all
select 5,1,'item1','itemdescription1','sub2.2',3 union all
select 6,1,'item1','itemdescription1','sub2.3',3 union all
select 7,2,'item2','itemdescription1','sub1',2 union all
select 8,2,'item2','itemdescription1','sub2',2 union all
select 9,3,'item3','itemdescription1','sub1',1 union all
select 10,3,'item3','itemdescription1','sub2',1 union all
select 11,3,'item3','itemdescription1','sub3',1 union all
select 12,3,'item3','itemdescription1','sub4',1

The select looks like below 

seqid   |itemid     |itemname   |itemdescription    |subitemname    |columncount
----------------------------------------------------------------------------------
1       |1      |item1      |itemdescription1   |sub1       |3
2       |1      |item1      |itemdescription1   |sub2       |3
3       |1      |item1      |itemdescription1   |sub3       |3
4       |1      |item1      |itemdescription1   |sub2.1     |3
5       |1      |item1      |itemdescription1   |sub2.2     |3
6       |1      |item1      |itemdescription1   |sub2.3     |3
7       |2      |item2      |itemdescription1   |sub1       |2
8       |2      |item2      |itemdescription1   |sub2       |2
9       |3      |item3      |itemdescription1   |sub1       |1
10      |3      |item3      |itemdescription1   |sub2       |1
11      |3      |item3      |itemdescription1   |sub3       |1
12      |3      |item3      |itemdescription1   |sub4       |1

And it needs to be partitioned such a way that the columncount column value represents the column count of sub items per item (toatal subitems/column count = total sub item rows ) (i.e,) item name with 2 column count would have 2 column for sub items in rows.

The partition result for the above data needs to look like this with RowId column which represent the sub items row position.

seqid   |itemid |itemname   |itemdescription    |subitemname    |fetchcount |RowID
----------------------------------------------------------------------------------
1   |1  |item1  |itemdescription1   |sub1   |3  |1
2   |1  |item1  |itemdescription1   |sub2   |3  |1
3   |1  |item1  |itemdescription1   |sub3   |3  |1
4   |1  |item1  |itemdescription1   |sub2.1 |3  |2
5   |1  |item1  |itemdescription1   |sub2.2 |3  |2
6   |1  |item1  |itemdescription1   |sub2.3 |3  |2
7   |2  |item2  |itemdescription1   |sub1   |2  |1
8   |2  |item2  |itemdescription1   |sub2   |2  |1
9   |3  |item3  |itemdescription1   |sub1   |1  |1
10  |3  |item3  |itemdescription1   |sub2   |1  |2
11  |3  |item3  |itemdescription1   |sub3   |1  |3
12  |3  |item3  |itemdescription1   |sub4   |1  |4

I tried with ranks or row_number and cte windowing with lot of combinations but didn't get expected result. Also not willing to use loop or cursors to update this RowID of the sub item. Please help.

Upvotes: 2

Views: 1196

Answers (1)

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Try this, this will work on the sample data you provided.

select *
    ,DENSE_RANK() OVER(PARTITION BY itemid 
                        ORDER BY (CASE WHEN columncount >1 
                        THEN LEFT(subitemname,LEN(subitemname)-1) 
                        ELSE subitemname END) ) AS  RowID1
from    @example
ORDER BY seqid

Upvotes: 1

Related Questions