Sql-server – SQL Server self recursive horizontal join

pivotsql server

I need to generate a query which provides hierarchical data in a horizontal way. The levels of depth are variable, it is ok if there are many empty fields. The only rows that should exist should be just children. Here's an example of what I need:

ID   |Value  |ID_Parent
-----|-------|---------
1    |NULL   |NULL
2    |NULL   |1
3    |30     |1
4    |NULL   |2
5    |20     |4
6    |10     |NULL

So the output to this would be something like:

ID   |Value  |Ancestor_1 |Ancestor_2 |Ancestor_3
-----|-------|-----------|-----------|----------
3    |30     |1          |NULL       |NULL
5    |20     |1          |2          |4
6    |10     |NULL       |NULL       |NULL

It is needed that the ancestors are labeled 1-to-n as farest-to-nearest.
A tree view of the data would be this one:

1->/2->4->5
   \3
6

I've found a solution for fixed levels but I was wondering if this can be done in SQL Server.

Best Answer

I feel there are two approaches - left to right starting anywhere that has no parent, and right to left for anywhere that has a value. I'm going to go with the latter because it matches the rows to be produced.

You should start by using a recursive CTE to turn your data into:

RowVal, NodeVal, Value, Level
3, 3, 30, 0
3, 1, 30, 1
5, 5, 20, 0
5, 4, 20, 1
5, 2, 20, 2
5, 1, 20, 3
6, 6, 10, 0

There are plenty of examples for doing this...

WITH theTree AS (
    SELECT ID AS RowVal, ID AS NodeVal, Value, 0 AS Level, ID_Parent
    FROM theTable 
    WHERE Value IS NOT NULL
    UNION ALL
    SELECT tr.RowVal, ta.ID AS NodeVal, tr.Value, Level + 1 AS Level, ta.ID_Parent
    FROM theTree AS tr
    JOIN theTable AS ta ON ta.ID = tr.ID_Parent
)
SELECT RowVal, NodeVal, Value, Level
FROM theTree
;

Now you need to adjust the Levels, because we started at the leaves.

MAX(Level) OVER (PARTITION BY RowVal) - Level AS NewLevel

Now it's simple dynamic pivot stuff, which you can find very easily. Group by RowVal, Value. A column per NewLevel. And MAX(NodeVal) in each "cell".

Related Solutions

Sql-server – Build a three table join with a recusive table in the middle

You can do the recursion in a CTE from the top down carrying MarkupGroupID with you.

with C as
(
  select P.PartGroupID,
         P.ParentID,
         P.MarkupGroupID
  from PartGroup as P
  where P.ParentID is null
  union all 
  select P.PartGroupID,
         P.ParentID,
         coalesce(P.MarkupGroupID, C.MarkupGroupID)
  from PartGroup as P 
    inner join C 
      on P.ParentID = C.PartGroupID
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  inner join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

SQL Fiddle

The recursive CTE will create a derived table that looks like this.

PartGroupID ParentID    MarkupGroupID
----------- ----------- -------------
1           NULL        NULL
2           NULL        1
3           NULL        2
15          3           2
16          3           2
17          3           2
4           1           3
5           1           4
8           5           4
13          8           4
14          8           4
6           4           3
7           4           3
11          7           3
12          7           3
9           6           3
10          6           3

If you need parts with partgroup not connected to a markup you can use a outer join against MarkupGroup in your main query.

with C as
(
  select ...
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  left outer join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

Sql-server – Join Calender Table

I think you want (tested at SQL-Fiddle-1):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(SUM(dt.the_count), 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
    LEFT JOIN 
        datatable AS dt
            ON  dt.the_date = ct.the_day
            AND dt.the_id = dd.the_id 
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
GROUP BY 
        dd.the_id, ct.the_day
ORDER BY 
        the_id, the_day ;

It can also be written like this (SQL-Fiddle-2):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(
            ( SELECT SUM(dt.the_count)
              FROM   datatable AS dt
              WHERE  dt.the_date = ct.the_day
                AND  dt.the_id = dd.the_id     
            )               
                            , 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
ORDER BY 
        the_id, the_day ;

Best Answer

Related Solutions

Sql-server – Build a three table join with a recusive table in the middle

Sql-server – Join Calender Table

Related Question