June 15, 2011 at 8:38 pm
I have a problem. My SQL:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Items]') AND type in (N'U'))
DROP TABLE [Items]
GO
CREATE TABLE [Items](
[ItemName] [varchar](50) NULL,
[ItemCode] [int] NULL,
[IdFolder] [int] NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Folders]') AND type in (N'U'))
DROP TABLE [Folders]
GO
CREATE TABLE [Folders](
[IdFolder] [int] NULL,
[IdFolderParent] [int] NULL,
[FolderName] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO Folders(IdFolder,IdFolderParent,FolderName)
SELECT 1,NULL,'Root'
UNION ALL SELECT 2,1,'A'
UNION ALL SELECT 3,1,'B'
UNION ALL SELECT 4,1,'C'
UNION ALL SELECT 5,2,'A1'
UNION ALL SELECT 6,2,'A2'
UNION ALL SELECT 7,2,'A3'
UNION ALL SELECT 8,5,'A1.1'
UNION ALL SELECT 9,6,'A2.1'
UNION ALL SELECT 10,6,'A2.2'
SELECT * FROM Folders
INSERT INTO Items(ItemCode,IdFolder,ItemName)
SELECT 1,9,'Ronaldo'
UNION ALL SELECT 2,10,'Messi'
UNION ALL SELECT 3,8,'Beck'
SELECT * FROM Items
I can write a SQL export data like this:
A
--A1
----A1.1
------Beck
--A2
----A2.1
------Ronaldo
----A2.2
------Messi
--A3
B
C
But my Boss want order by ItemCode and result like this:
A
--A2
----A2.1
------Ronaldo
----A2.2
------Messi
--A1
----A1.1
------Beck
--A3
B
C
Please help me :(.
June 15, 2011 at 10:29 pm
Post the code you've written to do the first export you talked about and we'll show you how to modify it to do what you want.
--Jeff Moden
June 15, 2011 at 10:51 pm
For result:
A
--A1
----A1.1
------Beck
--A2
----A2.1
------Ronaldo
----A2.2
------Messi
--A3
B
C
I have a SQL:
DECLARE @Folder_Item TABLE(
IdFolder int,
FolderName varchar(1024),
IdFolderParent int,
ItemCode nvarchar(7)
)
INSERT INTO@Folder_Item (
IdFolder,
FolderName,
IdFolderParent,
ItemCode
)
SELECTIdFolder,
FolderName,
IdFolderParent,
'' as ItemCode
FROMFolders
UNIONALL
SELECTIdFolder*10000+ItemCode,
ItemName,
IdFolder,
ItemCode
FROMItems
;WITH FamilyNode AS
(
(SELECT Folder.IdFolder,
Folder.FolderName,
Folder.IdFolderParent,
Folder.ItemCode,
1 AS LevelRoot,
CAST(ROW_NUMBER() OVER (ORDER BY FolderName ASC) as varchar(255)) AS Code
FROM@Folder_Item Folder
WHERE(Folder.IdFolderParent = 1)
)
UNIONALL
(SELECT Child.IdFolder,
Child.FolderName,
Child.IdFolderParent,
Child.ItemCode,
(Father.LevelRoot + 1) AS LevelRoot,
CAST(Father.Code + '.' + CAST(ROW_NUMBER() OVER (ORDER BY Child.FolderName ASC) as varchar(255)) as varchar(255)) as Code
FROM@Folder_Item Child
INNER JOIN FamilyNode Father
ONFather.IdFolder = Child.IdFolderParent
)
)
SELECT
REPLICATE('--', LevelRoot-1) + FolderName AS FolderName
FROMFamilyNode
ORDER BYCode
June 15, 2011 at 10:52 pm
On second thought, I'd better ask... is the order of the hierarchy based on the number of subnodes that each level 2 node has under it?
--Jeff Moden
June 15, 2011 at 10:59 pm
ah, My customer make sure: no data like this:
A
--A1
----A1.1
--A2
----A2.2
------Item code = 3
------A2.2.1
--------Item code = 1
--A3
B
--Item code = 2
C
Every data input are reasonable.
June 15, 2011 at 11:04 pm
nguyennd (6/15/2011)
ah, My customer make sure: no data like this:
A
--A1
----A1.1
--A2
----A2.2
------Item code = 3
------A2.2.1
--------Item code = 1
--A3
B
--Item code = 2
C
Every data input are reasonable.
That data doesn't match the original data you posted. For example, there is no subnode for "B". However, I believe I understand what you want. I'll be back in about 10 minutes or so.
--Jeff Moden
June 15, 2011 at 11:12 pm
Here you go... uses your original data and will, of course, handle additions such as subnodes on any other nodes including "B".
WITH
cteGetAllData (ChildID, ParentID, ChildName) AS
(
SELECT IdFolder,IdFolderParent,FolderName
FROM dbo.Folders
UNION ALL
SELECT -ItemCode,IdFolder,ItemName
FROM dbo.Items
)
,
cteBuildPath AS(
SELECT anchor.ChildID,
anchor.ParentID,
anchor.ChildName,
CAST(CAST(anchor.ChildID AS BINARY(4)) AS VARBINARY(4000)) AS HierarchyPath,
1 AS HierarchyLevel
FROM cteGetAllData anchor
WHERE ParentID = (SELECT ChildID FROM cteGetAllData WHERE ChildName = 'Root')
UNION ALL ---------------------------------------------------------------------------------------
SELECT recur.ChildID,
recur.ParentID,
recur.ChildName,
CAST(rcte.HierarchyPath + CAST(recur.ChildID AS BINARY(4)) AS VARBINARY(4000)) AS HierarchyPath,
rcte.HierarchyLevel + 1 AS HierarchyLevel
FROM cteGetAllData recur
INNER JOIN cteBuildPath rcte
ON rcte.ChildID = recur.ParentID
)
SELECT *,
REPLICATE('--',HierarchyLevel-1)
+ CASE WHEN ChildID >= 0 THEN ChildName ELSE 'Item Code = ' + CAST(-ChildID AS VARCHAR(10)) END
FROM cteBuildPath
ORDER BY HierarchyPath
;
I left all the columns in just so you can see what's going on. Remove the "*," to get just what you want.
--Jeff Moden
June 16, 2011 at 3:06 am
Thank for you post. But i need result like this:
A
--A2
----A2.1
------Item code = 1
----A2.2
------Item code = 2
--A1
----A1.1
------Item code = 3
--A3
B
C
Please help/support me.
June 16, 2011 at 7:18 am
You didn't read the entire post! 😉 I told you how to get that. Just remove the "*," from the code. 😉
--Jeff Moden
June 16, 2011 at 11:16 am
Jeff Moden (6/16/2011)
You didn't read the entire post! 😉 I told you how to get that. Just remove the "*," from the code. 😉
Nice...and with 2 minutes to spare 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 5:21 pm
opc.three (6/16/2011)
Jeff Moden (6/16/2011)
You didn't read the entire post! 😉 I told you how to get that. Just remove the "*," from the code. 😉Nice...and with 2 minutes to spare 😉
Heh... I aim to please. I sometimes miss, but I'm always aiming. 😛
--Jeff Moden
June 16, 2011 at 7:10 pm
Hi Jeff Moden.
I'm remove *, from the code and result is:
A
--A1
----A1.1
------Item Code = 3
--A2
----A2.1
------Item Code = 1
----A2.2
------Item Code = 2
--A3
B
C
But i need result is:
A
--A2
----A2.1
------Item Code = 1
----A2.2
------Item Code = 2
--A1
----A1.1
------Item Code = 3
--A3
B
C
You can see item code is order by 1 -> 2 -> 3.
Please support me, hic.
June 16, 2011 at 7:57 pm
nguyennd (6/16/2011)
Hi Jeff Moden.I'm remove *, from the code and result is:
A
--A1
----A1.1
------Item Code = 3
--A2
----A2.1
------Item Code = 1
----A2.2
------Item Code = 2
--A3
B
C
But i need result is:
A
--A2
----A2.1
------Item Code = 1
----A2.2
------Item Code = 2
--A1
----A1.1
------Item Code = 3
--A3
B
C
You can see item code is order by 1 -> 2 -> 3.
Please support me, hic.
Not according to the data you provided. The Item Codes I posted are attached to their respective superiors by the data you have in the Items table in the "InFolder" column.
Never mind. I see what you're doing. This won't be so easy.
--Jeff Moden
June 16, 2011 at 8:12 pm
Ok... I've got it. It'll take me a bit to modify the code.
--Jeff Moden
June 16, 2011 at 9:38 pm
Crud. I knew this looked all too familiar. We have a process at work that I developed to do something very, very similar and there's a patent pending on it. For me to show you how to solve your problem would 1) make the patent impossible, 2) put me out of a job because of an NDA violation, and 3) make it impossible for me to get another job because the NDA violation would follow me forever.
My most sincere apologies but someone else will have to do this for you.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply