CODEWITHSUNDEEP
sqlsql-server-2008
Sarat
Sarat

Reputation: 23

Any way to generate hierarchical values in sql server 2008

We need the values in hierarchical data format as below using SQL Server,

1
1.1
1.1.1
1.1.1.1
1.1.1.2
1.1.1.3
1.1.2
1.1.2.1
1.1.2.2
1.1.2.3
1.1.3
1.1.3.1
1.1.3.2
1.1.3.3
1.1.4
1.1.4.1
1.1.4.2
1.1.4.3
1.2
1.2.1
1.2.1.1
1.2.1.2
1.2.1.3
1.2.2
1.2.2.1
1.2.2.2
1.2.2.3
1.2.3
1.2.3.1
1.2.3.2
1.2.3.3
1.2.4
1.2.4.1
1.2.4.2
1.2.4.3
.
.
1.100.100.100

Can anyone help?

Upvotes: 0

Views: 362

Answers (2)

Paul Williams
Paul Williams

Reputation: 17020

You did not specify what data type you wanted as the output. I am assuming you want strings ordered as you specified in the form 1.X.X.X.

There are a couple of ways to approach this. A compact version would be to cross join the numbers 0 to 100 together several times:

;with Numbers (n) as
(
    select 0   -- base case: 0
    union all
    select n + 1 from Numbers where n < 100  -- recursive case: numbers 1 to 100
)
select
    cast (LV1.n as varchar) +
    case LV2.n when 0 then '' else '.' + cast (LV2.n as varchar) end +
    case LV3.n when 0 then '' else '.' + cast (LV3.n as varchar) end +
    case LV4.n when 0 then '' else '.' + cast (LV4.n as varchar) end
from Numbers LV1
    cross join Numbers LV2
    cross join Numbers LV3
    cross join Numbers LV4
where
    LV1.n = 1
    and
    (
              (LV2.n = 0 and LV3.n = 0 and LV4.n = 0)    -- 1.0.0.0 is OK
          or  (LV2.n <> 0 and LV3.n = 0 and LV4.n = 0)   -- 1.X.0.0 is OK
          or  (LV2.n <> 0 and LV3.n <> 0 and LV4.n = 0)  -- 1.X.X.0 is OK
          or  (LV2.n <> 0 and LV3.n <> 0 and LV4.n <> 0) -- 1.X.X.X is OK
    )
order by LV1.n, LV2.n, LV3.n, LV4.n

This example uses a recursive Common Table Expression (CTE) to generate the numbers 0 to 100. The code then cross joins these numbers together 4 times to make a pattern of X.X.X.X.

The where clause limits the output to all cases where the first number is a 1 and there is not a non-zero value after a zero value.

The select converts a zero value into a blank. So 1.0.0.0 gets translated into "1", 1.1.0.0 is converted to "1.1", etc.

When run on my development machine against SQL Server 2008 R2 Developer Edition SSMS query window, this starts to return data in 3 seconds. It finishes returning 1 million records in about 15 seconds.

Upvotes: 2

bdukes
bdukes

Reputation: 155935

SQL Server 2008 introduced the HeirarchyId data type to represent hierarchical data.

Upvotes: 1

Related Questions