Hi Yoshitha, HTH
[code]
SELECT 1 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'CEO' and branch='Hyderabad'
UNION ALL
SELECT 2 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'BM' and branch='Hyderabad'
SELECT 3 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'PL' and branch='Hyderabad'
SELECT 4 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'Tester' and branch='Hyderabad'
SELECT 5 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'PRG' and branch='Hyderabad'
UNION ALL
SELECT 6 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'CEO' and branch='Visakhapatnam'
UNION ALL
SELECT 7 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'BM' and branch='Visakhapatnam'
SELECT 8 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'PL' and branch='Visakhapatnam'
SELECT 9 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'Tester' and branch='Visakhapatnam'
SELECT 10 as CustomGroup,empname,designation,branch
FROM MyTable
WHERE designation = 'PRG' and branch='Visakhapatnam'
ORDER BY CustomGroup
[/code]

Cristian Lefter, SQL Server MVP