CODEWITHSUNDEEP
sqlsql-server
John Doe
John Doe

Reputation: 139

Unpivot N columns into one row

I have a table that holds all folder's id and their corresponding parent id.

I want to export all parent folders of a folder, but I need these folders to just be in one column.

I am trying to use a pivot table for this but it will not work for some reason. All pivot examples online contain aggregative functions. Do I have to implement one in as well for this to work? I don't think I do.

SELECT * FROM
(SELECT F.Id, F1.Id, F2.Id, F3.Id, F4.Id, F5.Id, F5.ParentId
FROM Folder F 
    LEFT JOIN Folder F1 ON F1.Id = F.ParentId
    LEFT JOIN Folder F2 ON F2.Id = F1.ParentId
    LEFT JOIN Folder F3 ON F3.Id = F2.ParentId
    LEFT JOIN Folder F4 ON F4.Id = F3.ParentId
    LEFT JOIN Folder F5 ON F5.Id = F4.ParentId
WHERE F.Id = '65D33613-9476-4BC8-90AA-2DA622CAA7CD') AS Folders
UNPIVOT(
Idens FOR F.Id IN (Id)
) AS PivotTable

Upvotes: 1

Views: 51

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use recursive common table expression instead of UNPIVOT.

;WITH CTE AS (
    SELECT *, 1 RN FROM Folder WHERE Id = '65D33613-9476-4BC8-90AA-2DA622CAA7CD'
    UNION ALL 
    SELECT F.Id,  F.ParentID, RN+1 RN FROM Folder F INNER JOIN CTE ON F.Id = CTE.ParentID
)
SELECT 'Id_' + CONVERT(VARCHAR,RN) Idens ,  Id FROM CTE
UNION ALL
SELECT * FROM (SELECT TOP 1 'ParentId' Idens , Id FROM CTE ORDER BY RN DESC) AS Parent

Upvotes: 1

Related Questions