hierarchy level - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

hierarchy level

Author

Topic

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-28 : 07:56:23

Hi

i tried on CTE but iam not gettng correct result plz help me

Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int)
Insert into @Tmaster
Select 1, 'Fixed', 14 Union All
Select 2, 'Flexible',14 union All
Select 3, 'Others',14 union All
Select 13, 'Retirement benefits',1 union All
Select 14, 'PBHeads', 0

Select * From @Tmaster

Declare @TDetails Table ( MasterId Int, Description varchar(100))
Insert into @TDetails
Select 1, 'Basic' union all
Select 1,'Conveyance' union all
Select 1,'HRA' union all
Select 1,'Special Allowance' union all
Select 2, 'Children Education' union all
Select 2,'Travel Allowance' union all
Select 2,'Medical Expenses' union all
Select 2,'Variable Spl Pay' union all
Select 3, 'Project Allowance' union all
Select 3,'Quarterly Incentive' union all
Select 3,'Shift Allowance' union all
Select 3,'Annual Component' union all
Select 13,'Provident Fund' union all
Select 13,'Gratuity' union all
Select 13,'Super annuation'

Select * From @TDetails

in group list we have to show fixed, flexible, other
in subgroups(L1) list under fixed we have to show
Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits

in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits

output :
Group Subgroup(L1) Subgroup(L2)
Fixed
Basic salary
HRA
Conveyance
Special Allowance
Retirement benefits
Provident Fund
Gratuity
Super annuation

Flexible reimbursements
LTA/Group Travel
Medical expenses
Children education
variable special pay

Others
Project allowance
Performance incentive
Medical insurance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 08:00:40

See Books Online for recursive CTE.

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-28 : 08:03:05

i tried but not getting the records correctly

;WITH Modules ( ParentMasterId, Type, MasterId, Descriptions) AS
(
SELECT LM.ParentMasterId,LM.Type, LM.MasterId, LD.Description
FROM @TMaster LM
INNER JOIN @TDetails ld on LM.MasterId = LD.MasterId
--WHERE ParentMasterId = @MasterId

UNION ALL

SELECT LM.ParentMasterId,LM.Type, LM.MasterId, LD.Description
FROM @TDetails ld
INNER JOIN @TMaster LM ON LM.MasterId = LD.MasterId
INNER JOIN Modules m ON M.MasterId = LM.ParentMasterId
--INNER JOIN Modules m ON M.parentMasterId = LM.MasterId

)
SELECT Distinct * FROM Modules

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 08:25:28

[code];
With CTE (Level,ID,Type,Descp,Parent) AS
(
SELECT 0,tm.MasterId,tm.[Type],td.[Description],tm.ParentMasterId
FROM @TMaster tm
LEFT JOIN @TDetails td
ON td.MasterId=tm.MasterId
WHERE tm.ParentMasterId=0
UNION ALL
SELECT c.Level+1,tm.MasterId,tm.[Type],td.[Description],tm.ParentMasterId
FROM @TMaster tm
INNER JOIN @TDetails td
ON td.MasterId=tm.MasterId
INNER JOIN CTE c
ON c.ID=tm.ParentMasterId
)

select Level,Type,Descp
FROM CTE c[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 08:27:02

For more efficiency, join the details table last, outside the cte.

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-28 : 08:42:14

No My output not like that

for the type Fixed you have to show
Basic
Conveyance
HRA
Special Allowance
Retirement benefits
in Description column

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 08:45:51

Please post proper output based on the sample data given about for @TDetails and @TMaster tables.

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-28 : 08:56:51

parentid type masterid description
14 Fixed 1 Basic
14 Fixed 1 Conveyance
14 Fixed 1 HRA
14 Fixed 1 Special Allowance
1 fixed 13 Retirement benefits
14 Flexible 2 Children Education
14 Flexible 2 Medical Expenses
14 Flexible 2 Travel Allowance
14 Flexible 2 Variable Spl Pay
14 Others 3 Annual Component
14 Others 3 Other Allowance Test
14 Others 3 Project allowance
14 Others 3 Quarterly Incentive
14 Others 3 Shift Allowance
1 Retirement benefits 13 Gratuity
1 Retirement benefits 13 Provident Fund
1 Retirement benefits 13 Super annuation

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 09:15:17

[code]-- Prepare sample data
DECLARE @Master TABLE (MasterID INT, Type VARCHAR(100), ParentMasterID INT)

INSERT @Master
SELECT 1, 'Fixed', 14 UNION ALL
SELECT 2, 'Flexible', 14 UNION ALL
SELECT 3, 'Others', 14 UNION ALL
SELECT 13, 'Retirement benefits', 1 UNION ALL
SELECT 14, 'PBHeads', 0

DECLARE @Details TABLE (MasterID INT, Description VARCHAR(100))

INSERT @Details
SELECT 1, 'Basic' UNION ALL
SELECT 1, 'Conveyance' UNION ALL
SELECT 1, 'HRA' UNION ALL
SELECT 1, 'Special Allowance' UNION ALL
SELECT 2, 'Children Education' UNION ALL
SELECT 2, 'Travel Allowance' UNION ALL
SELECT 2, 'Medical Expenses' UNION ALL
SELECT 2, 'Variable Spl Pay' UNION ALL
SELECT 3, 'Project Allowance' UNION ALL
SELECT 3, 'Quarterly Incentive' UNION ALL
SELECT 3, 'Shift Allowance' UNION ALL
SELECT 3, 'Annual Component' UNION ALL
SELECT 13, 'Provident Fund' UNION ALL
SELECT 13, 'Gratuity' UNION ALL
SELECT 13, 'Super annuation'

-- Prepare CTE
;WITH Yak (MasterID, ParentMasterID, Type)
AS (
SELECT MasterID,
ParentMasterID,
Type
FROM @Master
WHERE ParentMasterID = 0

UNION ALL

SELECT m.MasterID,
m.ParentMasterID,
m.Type
FROM @Master AS m
INNER JOIN Yak AS y ON y.MasterID = m.ParentMasterID
)

-- Show the expected output
SELECT y.ParentMasterID AS ParentID,
y.Type,
y.MasterID,
d.Description
FROM Yak AS y
INNER JOIN @Details AS d ON d.MasterID = y.MasterID
ORDER BY y.MasterID,
d.Description[/code]

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-28 : 09:33:31

thanks peso,
my output and u r output are same but my result not like that

my output is as follows