How to Create an Unbalanced Hierarchy from a String in T-SQL

Originally Posted on December 15 2011

Just for one time, I dismiss my usual business attire to publish a very technical post, rooted in the real life of my “other” job. I hope it could be of help to someone.

Few days ago I had to implement a new conformed dimension. For reasons too long to describe here, I couldn’t load a table but I had to use a view from existing tables in a SQL server linked server.

The dimension hierarchy was available as a string, with separators among the elements.

For example “Food/Fruits/Peaches/Nectarines”

Also I had rows like “Food/Mineral Water”

There were also rows like “Food/Confectionary/Desserts/Pudding/Chocolate Pudding”

In a word, the hierarchy was unbalanced. So, to add it to a conformed dimension, I had to split the string and balance the hierarchy to look like the table below; and I had to do it on the fly.

Table.png


My solution was to implement a function in T-SQL.


USE [<your database>]

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE FUNCTION dbo.[H_Element_Get]

(   

    @RowData NVARCHAR(MAX),

    @Sep NVARCHAR(MAX),

    @Ord INT

)

RETURNS  NVARCHAR (MAX)

 

AS

BEGIN

    DECLARE @Iter INT

    DECLARE @sstring nvarchar (max)

    SET @Iter = 1

 

    DECLARE @FoundIndex INT

    SET @FoundIndex = CHARINDEX(@Sep,@RowData)

 

    WHILE (@Iter<=@Ord)

    BEGIN

        IF @FoundIndex>0

                  BEGIN

                        SELECT

                             @sstring = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

 

SET @RowData = SUBSTRING(@RowData,@FoundIndex + DATALENGTH(@Sep) / 2,LEN(@RowData))

 

                        SET @FoundIndex = CHARINDEX(@Sep, @RowData)

                  END

        ELSE

                  BEGIN

                        SELECT @sstring = LTRIM(RTRIM(@RowData))

                  END

      

        SET @Iter = @Iter + 1

    END

 

    RETURN @sstring

END

GO

 

 

 

 

The [H_Element_Get] function accepts 3 parameters:


@RowData = the string of chained elements

@sep = the separator

@Ord = the hierarchy level to retrieve

 

The maximum hierarchy depth is known, of course, and it is not subject to change without notice.


The select statement for, say, a four level hierarchy may look like the following:


SELECT

      H_Element_Get(<string>,<separator>,1) as [Level 1],

      H_Element_Get(<string>,<separator>,2) as [Level 2],

      H_Element_Get(<string>,<separator>,3) as [Level 3],

      H_Element_Get(<string>,<separator>,4) as [Level 4]

 

Let me know if I can be of further help.


See you next time.



290 views and 2 responses

  • Dic 16 2011, 9:11 PM

    Yuri Fal liked this post.

  • Dic 20 2011, 3:22 AM

    Ellie K liked this post.