January 19, 2014 at 12:54 pm
I have an existing function and need to alter function to give result of the values parent-description until its parent is reached.
--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]
---INSERT DATA
insert into CityData
values(1,-1,'Bangalore','BangaloreMain',20),
(2,1,'NULL','North Bangalore',10),
(3,2,'NULL','North Bangalore - Area-1',5),
(4,2,'NULL','North Bangalore - Area-2',15),
(5,1,'NULL','South Bangalore',7),
(6,-1,'Others','Coimbatore',4),
(7,6,'NULL','Coimbatore South',5),
(8,7,'NULL','Tirupur',1),
(9,7,'NULL','Avinashi',3)
----CREATE FUNCTION
CREATE FUNCTION [dbo].[GetFirstLevelLabel]
(
@cityParentId int
)
RETURNS nvarchar(128)
AS
BEGIN
DECLARE @strLabel nvarchar(128)
set @strLabel = ''
if @cityId >= 999999
set @strLabel = SELECT City FROM CityData where Id = @cityParentId
-- Need help on getting the parent id -1 in loop
else
if @depName <> ''
set @strLabel = SELECT City FROM CityData where Id = @cityParentId
else
set @strLabel = 'General'
RETURN @strLabel
END
GO
---- My Query
SELECT Id, GetFirstLevelLabel(ParentID) as City, Location, Amt
, Amt
FROM CityData
Result Expected
City Location Amt
Bangalore BangaloreMain 20
Bangalore North Bangalore 10
Bangalore North Bangalore - Area-1 5
Bangalore North Bangalore - Area-2 15
Bangalore South Bangalore 7
Others Coimbatore 4
Others Coimbatore South 5
Others Tirupur 1
Others Avinashi 3
January 19, 2014 at 3:04 pm
Thank you very much for the readily consumable test data. It does make things much more clear and easier to concentrate on code instead of building test data.
Your requested output actually has a small error in it. The South Bangalore (value = 7) has an error because it's actually a child of Bangalore (value = 20). The output for South Bangalore should be 20*7 or 140. With that in mind, the following code will do as you wish. You just need to replace the code you have in your recursive function (which is VERY expensive, BTW) with the following. Of course, I've included the test data, as well.
--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]
;
---INSERT DATA
insert into CityData
SELECT 1,-1,'Bangalore','BangaloreMain',20 UNION ALL
SELECT 2,1,'NULL','North Bangalore',10 UNION ALL
SELECT 3,2,'NULL','North Bangalore - Area-1',5 UNION ALL
SELECT 4,2,'NULL','North Bangalore - Area-2',15 UNION ALL
SELECT 5,1,'NULL','South Bangalore',7 UNION ALL
SELECT 6,-1,'Others','Coimbatore',4 UNION ALL
SELECT 7,6,'NULL','Coimbatore South',5 UNION ALL
SELECT 8,7,'NULL','Tirupur',1 UNION ALL
SELECT 9,7,'NULL','Avinashi',3
;
--===== Solve the problem
WITH
cteHierarchy AS
(--==== Get the "Root" row
SELECT ID, ParentID, City, Location, Amt, AmtProd = Amt,
SortPath = CAST(CAST(ID AS BINARY(4)) AS VARBINARY(4000))
FROM dbo.CityData
WHERE ParentID = -1
UNION ALL
--==== Recurse through each level of the hierarchy
SELECT t.ID, t.ParentID, h.City, t.Location, t.Amt, AmtProd = h.AmtProd * t.Amt,
SortPath = CAST(h.SortPath + CAST(t.ID AS BINARY(4)) AS VARBINARY(4000))
FROM dbo.CityData t
INNER JOIN cteHierarchy h ON t.ParentID = h.ID
)--==== Display the output from the CTE above
SELECT *
FROM cteHierarchy
ORDER BY SortPath
;
Note that I've left all working columns in the output. You can obviously do away with those in the final SELECT.
For more information on sorting the Hierarchy into the expected sort order, please see the following article.
http://qa.sqlservercentral.com/articles/T-SQL/72503/
Here's the output from the code above.
ID ParentID City Location Amt AmtProd SortPath
-- -------- --------- ------------------------ --- ------- --------------------------
1 -1 Bangalore BangaloreMain 20 20 0x00000001
2 1 Bangalore North Bangalore 10 200 0x0000000100000002
3 2 Bangalore North Bangalore - Area-1 5 1000 0x000000010000000200000003
4 2 Bangalore North Bangalore - Area-2 15 3000 0x000000010000000200000004
5 1 Bangalore South Bangalore 7 140 0x0000000100000005
6 -1 Others Coimbatore 4 4 0x00000006
7 6 Others Coimbatore South 5 20 0x0000000600000007
8 7 Others Tirupur 1 20 0x000000060000000700000008
9 7 Others Avinashi 3 60 0x000000060000000700000009
(9 row(s) affected)
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply