create serial numbers based on multiple columns

Question

I have a following data

I want to create the Index Level as shown in last column based on Region, Country, Store Type and Location column. So Region column will have its own serial number for each new region i.e. 'APAC is 1 and then EMEA will be 2' similarly its same for other columns(Country, Store Type, Location). I have tried to use partition to get the result but i am not able to get desired results.

Mike · Accepted Answer · 2016-11-13 12:04:44Z

3

Use dense_rank() window function.

select *,
       concat(
        dense_rank() over(order by Region), ',',
        dense_rank() over(partition by Region order by Country), ',',
        dense_rank() over(partition by Region, Country order by [Store Type]), ',',
        dense_rank() over(partition by Region, Country, [Store Type] order by Location)
       ) as [Index Level]
  from Tab

edited Nov 13, 2016 at 12:04

answered Nov 13, 2016 at 11:35

Mike

2,0051 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AdrianChodkowski · Accepted Answer · 2016-11-13 11:29:10Z

0

You use simple CONCAT function (https://msdn.microsoft.com/en-us/library/hh231515.aspx) or + operator to concatenate all the values together and divide all the values with comma.

answered Nov 13, 2016 at 11:29

AdrianChodkowski

1142 bronze badges

1 Comment

sudhakarssd Over a year ago

No i am not asking concatenating values. I want to create those serial numbers based on hierarchy.

Collectives™ on Stack Overflow

create serial numbers based on multiple columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related