title: What is the most efficient way to concatenate a string from all parent rows using T-SQL?
body: <p>I have a table that has a self-referencing foreign key that represents its parent row.  To illustrate the problem in its simplest form we'll use this table:</p>
  
  <pre><code>CREATE TABLE Folder(
      id int IDENTITY(1,1) NOT NULL, --PK
      parent_id int NULL,        --FK
      folder_name varchar(255) NOT NULL)
  </code></pre>
  
  <p>I want to create a scalar-valued function that would return a concatenated string of the folder's name and all its parent folder names all the way to the root folder, which would be designated by a null parent_id value.</p>
  
  <p>My current solution is a procedural approach which I assume is not ideal.  Here is what I'm doing:</p>
  
  <pre><code>CREATE FUNCTION dbo.GetEntireLineage
      (@folderId INT)
      RETURNS VARCHAR(MAX)
  AS
  BEGIN
      DECLARE @lineage VARCHAR(MAX)
      DECLARE @parentFolderId INT
  
      SELECT @lineage = folder_name, @parentFolderId = parent_id FROM Folder WHERE id = @folderId
  
  WHILE NOT @parentFolderId IS NULL
      BEGIN
          SET @parentFolderId = (SELECT parent_id FROM Folder WHERE parent_id = @parentFolderId)
          SET @lineage = (SELECT @lineage + '-' + (SELECT folder_name FROM Folder WHERE parent_id = @parentFolderId))
      END
  RETURN @lineage
  END
  </code></pre>
  
  <p>Is there a more ideal way to do this?  I'm an experienced programmer but T-SQL not a familiar world to me and I know these problems generally require a different approach due to the nature of set based data.  Any help finding a solution or any other tips and tricks to deal with T-SQL would be much appreciated.</p>