aashay uppala aashay uppala - 2 months ago 31
SQL Question

Group Numbers based on Level

I have requirement in SQL Server database where I have to group the data. please find the image attached for reference.

If the Level is more than 2 then it has to be grouped under level 2 (ex: 1.1.1, 1.1.2 are rolled up under 1.1.) and if there isn't any level 2 available then have to create a second level based on the level 3 Numbers (ex: 1.2.1)



Thanks

Answer

If it's not certain that the first 2 numbers are single digits:

declare @T table ([Column] varchar(20));

insert into @T values ('1'),('1.2.'),('1.2.3'),('1.2.3.4'),('10.20.'),('10.20.30'),('10.20.30.40');

select 
case when [Column] like '%.%.%' then substring([Column],1,charindex('.',[Column],charindex('.',[Column])+1)) else [Column] end as [Derived Col1], 
[Column] as [Derived Col2]
from @T;

If it's certain that the first 2 numbers are single digits then it can be simplified:

declare @T table ([Column] varchar(20));

insert into @T values ('1'),('1.2.'),('1.2.3'),('1.2.3.4');

select 
substring([Column],1,4) as [Derived Col1], 
[Column] as [Derived Col2]
from @T;
Comments