Select Tree and Order by from another table

Question

Select Tree and Order by from another table

nguyennd

SSCommitted

Points: 1881
More actions
June 15, 2011 at 8:38 pm

#389745

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 :(.
http://unitech.vn

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nguyennd SSCommitted Points: 1881 More actions · Answer 2

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

http://unitech.vn

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nguyennd SSCommitted Points: 1881 More actions · Answer 4

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.

http://unitech.vn

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nguyennd SSCommitted Points: 1881 More actions · Answer 7

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.

http://unitech.vn

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

You didn't read the entire post! 😉 I told you how to get that. Just remove the "*," from the code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nguyennd SSCommitted Points: 1881 More actions · Answer 11

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.

http://unitech.vn

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Ok... I've got it. It'll take me a bit to modify the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)