My table is
CREATE TABLE [ARQ_Arquivos] (
[ARQ_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ARQ_Descricao] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
[ARQ_Link] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[ARQ_DataHora] [datetime] NOT NULL ,
[ARQ_PastaID] [int] NULL ,
[ARQ_EPasta] [bit] NOT NULL ,
[ARQ_Criador] [int] NOT NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I need to make a folder tree in a sql query from that table where ARQ_ID is the father of
ARQ_PastaID and I need a result like
Folder1
Folder1/subfolder1
folder1/subfolder1/subfolder2
folder 2
folder 3
folder3/subfolder3
I tried with SELECT A.ARQ_Link, A.ARQ_PASTAID,
CASE WHEN A.ARQ_PASTAID IS NOT NULL THEN
(SELECT C.ARQ_Link FROM ARQ_Arquivos C WHERE C.ARQ_ID = A.ARQ_PASTAID)+'/'+A.ARQ_LINK
ELSE
A.ARQ_Link
END AS PASTA FROM ARQ_Arquivos A
INNER JOIN ARQ_Arquivos B
ON A.ARQ_ID = B.ARQ_PastaID and A.ARQ_EPasta = 1
but i repeat all files wrongly....and do for 1 level of subfolder only...how can I male it
select from the same table
Safa2535
If you are using 2005, then you can do something like this (from Pro SQL Server 2005 sample code):
Execute in AdventureWorks. The heirarchy column of the CTE accumulates the heirarchy:
--enhanced version of the CTE query
DECLARE @managerId int
SET @managerId = 140;
WITH EmployeeHierarchy(EmployeeID, ManagerID, treelevel, heirarchy)
AS
(
SELECT EmployeeID, ManagerID,
1 as treelevel, CAST(EmployeeId as varchar(max)) as heirarchy
FROM HumanResources.Employee as Employee
WHERE ManagerID=@managerId
UNION ALL
SELECT Employee.EmployeeID, Employee.ManagerID,
treelevel + 1 as treelevel,
heirarchy + '\' +cast(Employee.EmployeeId as varchar(20)) as heirarchy
FROM HumanResources.Employee as Employee
INNER JOIN EmployeeHierarchy
on Employee.ManagerID= EmployeeHierarchy.EmployeeID
)
SELECT Employee.EmployeeID,Contact.LastName,Contact.FirstName,
EmployeeHierarchy.treelevel, EmployeeHierarchy.heirarchy
FROM HumanResources.Employee as Employee
INNER JOIN EmployeeHierarchy
ON Employee.EmployeeID = EmployeeHierarchy.EmployeeID
INNER JOIN Person.Contact as Contact
ON Contact.contactId = employee.contactId
ORDER BY heirarchy
go
praveen kumar sharma
I suppose you cannot get what you want from an SQL query like that if you do not know the maximal level of nesting of folders beforehand. If you know, you will get an ugly code.
I suggest that you store the path from leaf to root in each row. subfolder2 in your example would have its fPATH equal to 'folder1/subfolder1' (or 'subfolder1/folder1', depending on how you store the paths-from leaf to root or vice versa), or even 'folder1/subfolder1/subfolder2' . This will add some redundancy to your data, but it's very effective. You will need to write a change to modify your tables, but it won't be hard.
Hope this helps.