SQL Server :Recursive CTE? Creating Report From an Organisation Structure

Question

I hope someone can help me. I want to have a Report with calculations for an organisation structure.

CREATE TABLE Organistation
  (
    KeySubCompany NUMERIC(12, 0) ,
    O1 VARCHAR(255) ,
    O2 VARCHAR(255) ,
    O3 VARCHAR(255) ,
    O4 VARCHAR(255) ,
    O5 VARCHAR(255) ,
    O6 VARCHAR(255) ,
    O7 VARCHAR(255) ,
    O8 VARCHAR(255) ,
    O9 VARCHAR(255) ,
    O10 VARCHAR(255) ,
    O11 VARCHAR(255) ,
    O12 VARCHAR(255)
  )

INSERT    INTO dbo.Organistation
          ( KeySubCompany, O1, O2, O3, O4, O5, O6, O7, O8, O9, O10, O11, O12 )
VALUES    ( 1, 'World', 'Europe', 'Germany', 'Berlin', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
          ( 2, 'World', 'Europe', 'Germany', 'Hamburg', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
          ( 3, 'World', 'Europe', 'Italy', 'Rom', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
          ( 4, 'World', 'Europe', 'Italy', 'Neapel', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
          ( 5, 'World', 'Europe', 'Germany', 'Dresden', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
          ( 6, 'World', 'North America', 'Canada', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
          ( 7, 'World', 'North America', 'USA', 'New York', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )        


CREATE TABLE COMPANY
  (
    KeyCompany NUMERIC(12, 0) ,
    NAME VARCHAR(255) ,
    viewdepth INTEGER
  )


INSERT    INTO dbo.COMPANY
          ( KeyCompany, NAME, viewdepth )
VALUES    ( 1, 'Main', 2 )

CREATE TABLE SUBCOMPANY
  (
    KeySubCompany NUMERIC(12, 0) ,
    KeyCompany NUMERIC(12, 0) ,
    NameSubCompany VARCHAR(255)
  )


INSERT    INTO dbo.SUBCOMPANY
          ( KeySubCompany, KeyCompany, NameSubCompany )
VALUES    ( 1, 1, '1_SubCompany' ),
          ( 2, 1, '2_SubCompany' ),
          ( 3, 1, '3_SubCompany' ),
          ( 4, 1, '4_SubCompany' ),
          ( 5, 1, '5_SubCompany' ),
          ( 6, 1, '6_SubCompany' ),
          ( 7, 1, '7_SubCompany' )

CREATE TABLE EMPLOYEES
  (
    EMPLOYID NUMERIC(12, 0) ,
    KeySubCompany NUMERIC(12, 0) ,
    NAME VARCHAR(255)
  )


INSERT    INTO dbo.EMPLOYEES
          ( EMPLOYID, KeySubCompany, NAME )
VALUES    ( 1, 1, 'Winny' ),
          ( 1, 1, 'Justin' ),
          ( 1, 3, 'Mary' ),
          ( 1, 4, 'Leon' ),
          ( 1, 4, 'Marcy' ),
          ( 1, 7, 'Peter' )

My desired result is:

enter image description here

In the table Company I want to be able to configure the depth of the organisation structure in my report (at the moment view depth 3)

My solution at the Moment:

WITH  CompanyRollup
    AS ( SELECT   C.KeyCompany ,
                  CASE WHEN ( GROUPING(O.O1) = 1 ) THEN ''
                       ELSE O.O1
                  END AS O1 ,
                  NULL AS O2 ,
                  NULL AS O3 ,
                  NULL AS O4 ,
                  NULL AS O5 ,
                  NULL AS O6 ,
                  NULL AS O7 ,
                  NULL AS O8 ,
                  NULL AS O9 ,
                  NULL AS O10 ,
                  NULL AS O11 ,
                  NULL AS O12 ,
                  1 AS viewDepth
         FROM     dbo.COMPANY C
                  INNER JOIN dbo.SUBCOMPANY SC ON SC.KeyCompany = C.KeyCompany
                                                  AND ( C.viewdepth + 1 ) = 1
                  INNER JOIN dbo.Organistation O ON O.KeySubCompany = SC.KeySubCompany
         GROUP BY C.KeyCompany ,
                  O.O1
                  WITH ROLLUP
         UNION
         SELECT   C.KeyCompany ,
                  CASE WHEN ( GROUPING(O.O1) = 1 ) THEN ''
                       ELSE O.O1
                  END AS O1 ,
                  CASE WHEN ( GROUPING(O.O2) = 1 ) THEN ''
                       ELSE O.O2
                  END AS O2 ,
                  NULL AS O3 ,
                  NULL AS O4 ,
                  NULL AS O5 ,
                  NULL AS O6 ,
                  NULL AS O7 ,
                  NULL AS O8 ,
                  NULL AS O9 ,
                  NULL AS O10 ,
                  NULL AS O11 ,
                  NULL AS O12 ,
                  2 AS viewDepth
         FROM     dbo.COMPANY C
                  INNER JOIN dbo.SUBCOMPANY SC ON SC.KeyCompany = C.KeyCompany
                                                  AND ( C.viewdepth + 1 ) = 2
                  INNER JOIN dbo.Organistation O ON O.KeySubCompany = SC.KeySubCompany
         GROUP BY C.KeyCompany ,
                  O.O1 ,
                  O.O2
                  WITH ROLLUP
         UNION
         SELECT   C.KeyCompany ,
                  CASE WHEN ( GROUPING(O.O1) = 1 ) THEN ''
                       ELSE O.O1
                  END AS O1 ,
                  CASE WHEN ( GROUPING(O.O2) = 1 ) THEN ''
                       ELSE O.O2
                  END AS O2 ,
                  CASE WHEN ( GROUPING(O.O3) = 1 ) THEN ''
                       ELSE O.O3
                  END AS O3 ,
                  NULL AS O4 ,
                  NULL AS O5 ,
                  NULL AS O6 ,
                  NULL AS O7 ,
                  NULL AS O8 ,
                  NULL AS O9 ,
                  NULL AS O10 ,
                  NULL AS O11 ,
                  NULL AS O12 ,
                  3 AS viewDepth
         FROM     dbo.COMPANY C
                  INNER JOIN dbo.SUBCOMPANY SC ON SC.KeyCompany = C.KeyCompany
                                                  AND ( C.viewdepth + 1 ) = 3
                  INNER JOIN dbo.Organistation O ON O.KeySubCompany = SC.KeySubCompany
         GROUP BY C.KeyCompany ,
                  O.O1 ,
                  O.O2 ,
                  O.O3
                  WITH ROLLUP
       )

SELECT  CR.KeyCompany ,
        SUM(SE.EMPLOYID) CountEmployees ,
        CR.O1 ,
        NULL AS O2,
        NULL AS O3,
        NULL AS O4,
        NULL AS O5,
        NULL AS O6,
        NULL AS O7,
        NULL AS O8,
        NULL AS O9,
        NULL AS O10,
        NULL AS O11,
        NULL AS O12
FROM    CompanyRollup CR
        INNER JOIN ( SELECT DISTINCT
                            KeyCompany ,
                            EMPLOYID
                     FROM   dbo.EMPLOYEES E
                            INNER JOIN dbo.SUBCOMPANY SSC ON SSC.KeySubCompany = E.KeySubCompany
                   ) SE ON SE.KeyCompany = CR.KeyCompany
        INNER JOIN ( SELECT SSSC.KeyCompany ,
                            SO.*
                     FROM   dbo.SUBCOMPANY SSSC
                            INNER JOIN dbo.Organistation SO ON SSSC.KeySubCompany = SO.KeySubCompany
                   ) SOO ON SOO.KeyCompany = CR.KeyCOmpany
                            AND ( CR.O1 = ''
                                  OR ISNULL(CR.O1, '') = ISNULL(SOO.O1, '')
                                )
WHERE   CR.Viewdepth = 1
GROUP BY CR.KeyCompany ,
        CR.O1 

  UNION
  SELECT  CR.KeyCompany ,
        SUM(SE.EMPLOYID) CountEmployees ,
        CR.O1 ,
        CR.O2 ,
        NULL  AS O3,
        NULL AS O4,
        NULL AS O5,
        NULL AS O6,
        NULL AS O7,
        NULL AS O8,
        NULL AS O9,
        NULL AS O10,
        NULL AS O11,
        NULL AS O12
FROM    CompanyRollup CR
        INNER JOIN ( SELECT DISTINCT
                            KeyCompany ,
                            EMPLOYID
                     FROM   dbo.EMPLOYEES E
                            INNER JOIN dbo.SUBCOMPANY SSC ON SSC.KeySubCompany = E.KeySubCompany
                   ) SE ON SE.KeyCompany = CR.KeyCompany
        INNER JOIN ( SELECT SSSC.KeyCompany ,
                            SO.*
                     FROM   dbo.SUBCOMPANY SSSC
                            INNER JOIN dbo.Organistation SO ON SSSC.KeySubCompany = SO.KeySubCompany
                   ) SOO ON SOO.KeyCompany = CR.KeyCOmpany
                            AND ( CR.O1 = ''
                                  OR ISNULL(CR.O1, '') = ISNULL(SOO.O1, '')
                                )AND ( CR.O2 = ''
                                  OR ISNULL(CR.O2, '') = ISNULL(SOO.O2, '')
                                )
WHERE   CR.Viewdepth = 2
GROUP BY CR.KeyCompany ,
        CR.O1,
        CR.O2 
    UNION
  SELECT  CR.KeyCompany ,
        SUM(SE.EMPLOYID) CountEmployees ,
        CR.O1 AS O1,
        CR.O2 AS O2,
        CR.O3 AS O3,
        NULL AS O4,
        NULL AS O5,
        NULL AS O6,
        NULL AS O7,
        NULL AS O8,
        NULL AS O9,
        NULL AS O10,
        NULL AS O11,
        NULL AS O12
FROM    CompanyRollup CR
        INNER JOIN ( SELECT DISTINCT
                            KeyCompany ,
                            EMPLOYID
                     FROM   dbo.EMPLOYEES E
                            INNER JOIN dbo.SUBCOMPANY SSC ON SSC.KeySubCompany = E.KeySubCompany
                   ) SE ON SE.KeyCompany = CR.KeyCompany
        INNER JOIN ( SELECT SSSC.KeyCompany ,
                            SO.*
                     FROM   dbo.SUBCOMPANY SSSC
                            INNER JOIN dbo.Organistation SO ON SSSC.KeySubCompany = SO.KeySubCompany
                   ) SOO ON SOO.KeyCompany = CR.KeyCOmpany
                            AND ( CR.O1 = ''
                                  OR ISNULL(CR.O1, '') = ISNULL(SOO.O1, '')
                                )AND ( CR.O2 = ''
                                  OR ISNULL(CR.O2, '') = ISNULL(SOO.O2, '')
                                ) AND ( CR.O3 = ''
                                  OR ISNULL(CR.O3, '') = ISNULL(SOO.O3, '')
                                )
WHERE   CR.Viewdepth = 3
GROUP BY CR.KeyCompany ,
        CR.O1,
        CR.O2,
        CR.O3.... UNION.... UNTIL O12

..... and so on until O12

As you can see it's almost only copy/paste just the organisation structure is a little bit different.

Is there a way to create a "cleaner" version to create a Report like that?

I've created a "simple" which has the same structure as my database/tables I have at the moment, so it's not possible to change the table structure.

Thanks in advance.

Before I forget SQL Fiddle Demo

Yes. Use a CTE. Read http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx and http://msdn.microsoft.com/en-gb/library/ms175972.aspx — podiluska, May 12 '14 at 08:56
hmm sorry iam kind of new to this.. ive checked it out but how can i create a report like that with a CTE? — WhoisIt, May 13 '14 at 06:54
There is no employee in Canada (KeySubCompany = 6) in your data, why the result shows 1? — Serpiton, May 13 '14 at 09:21

score 1 · Answer 1 · answered May 13 '14 at 08:17

Take a look at the things below. this is the easiest way to make tiered list that I know. If you can't change the underlying table structure it is still easier to prepare your data in #temp tables then to make 10 layers of nested CTEs.

SQLFiddle

CREATE TABLE Grp_lvl_1 (
  Id INT IDENTITY(1,1) NOT NULL,
  Name NVARCHAR(50))

CREATE TABLE Grp_lvl_2 (
  Id INT IDENTITY(1,1) NOT NULL,
  lvl_1_Id INT,
  Name NVARCHAR(50))

INSERT Grp_lvl_1 (Name) VALUES
('Continent_1'),
('Continent_2'),
('Continent_3')

INSERT Grp_lvl_2 (lvl_1_Id, Name) VALUES 
(1,'Country_1'),
(1,'Country_2'),
(1,'Country_3'),
(2,'Country_4'),
(2,'Country_5'),
(2,'Country_6'),
(3,'Country_7'),
(3,'Country_8'),
(3,'Country_9')

SELECT Grp_lvl_1.Name col1, Grp_lvl_2.Name col2
FROM Grp_lvl_1 INNER JOIN 
Grp_lvl_2 ON Grp_lvl_1.Id = Grp_lvl_2.lvl_1_Id

score 1 · Answer 2 · answered May 13 '14 at 10:14

No, there is no need for recursive CTE, a ROLLUP can get your report

If you are using SQLServer 2008 or better the list of condition in the HAVING can be substituted by a single condition using GROUPING_ID, it return the level of grouping in the ROLLUP, CUBE and GROUPING SETS, as the int value of a bitmap so the value need a conversion, the bitmap is in the same order of the columns, so the less the details you want to see the higher will be the value of GROUPING_ID to use as lower bound.

SELECT c.KeyCompany
     , COUNT(e.EMPLOYID) CountEmployees
     , o.o1, o.o2, o.o3, o.o4, o.o5, o.o6
     , o.o7, o.o8, o.o9, o.o10, o.o11, o.o12
FROM   Organistation o
       INNER JOIN SUBCOMPANY s ON o.KeySubCompany = s.KeySubCompany
       INNER JOIN COMPANY c ON s.KeyCompany = c.KeyCompany
       INNER JOIN EMPLOYEES e ON o.KeySubCompany = e.KeySubCompany
GROUP BY c.KeyCompany, RollUp(o.o1, o.o2, o.o3, o.o4, o.o5, o.o6
                            , o.o7, o.o8, o.o9, o.o10, o.o11, o.o12)
HAVING GROUPING(o.o1) = 0
   AND GROUPING_ID(o.o1, o.o2, o.o3, o.o4, o.o5, o.o6
                 , o.o7, o.o8, o.o9, o.o10, o.o11, o.o12)
     > power(2, 11 - (MAX(c.viewdepth) + 1))
ORDER BY COUNT(e.EMPLOYID)

SQLFiddle demo

If you are using SQLServer 2005 or an older versione the query will be more verbose

SELECT c.KeyCompany
     , COUNT(e.EMPLOYID) CountEmployees
     , o.o1, o.o2, o.o3, o.o4, o.o5, o.o6
     , o.o7, o.o8, o.o9, o.o10, o.o11, o.o12
FROM   Organistation o
       INNER JOIN SUBCOMPANY s ON o.KeySubCompany = s.KeySubCompany
       INNER JOIN COMPANY c ON s.KeyCompany = c.KeyCompany
       INNER JOIN EMPLOYEES e ON o.KeySubCompany = e.KeySubCompany
GROUP BY c.KeyCompany, RollUp(o.o1, o.o2, o.o3, o.o4, o.o5, o.o6
                            , o.o7, o.o8, o.o9, o.o10, o.o11, o.o12)
HAVING GROUPING(o.o1) = 0
   AND (GROUPING(o.o1) = 1 OR (GROUPING(o.o1) = 0 AND MAX(c.viewdepth) + 1 >= 1))
   AND (GROUPING(o.o2) = 1 OR (GROUPING(o.o2) = 0 AND MAX(c.viewdepth) + 1 >= 2))
   AND (GROUPING(o.o3) = 1 OR (GROUPING(o.o3) = 0 AND MAX(c.viewdepth) + 1 >= 3))
   AND (GROUPING(o.o4) = 1 OR (GROUPING(o.o4) = 0 AND MAX(c.viewdepth) + 1 >= 4))
   AND (GROUPING(o.o5) = 1 OR (GROUPING(o.o5) = 0 AND MAX(c.viewdepth) + 1 >= 5))
   AND (GROUPING(o.o6) = 1 OR (GROUPING(o.o6) = 0 AND MAX(c.viewdepth) + 1 >= 6))
   AND (GROUPING(o.o7) = 1 OR (GROUPING(o.o7) = 0 AND MAX(c.viewdepth) + 1 >= 7))
   AND (GROUPING(o.o8) = 1 OR (GROUPING(o.o8) = 0 AND MAX(c.viewdepth) + 1 >= 8))
   AND (GROUPING(o.o9) = 1 OR (GROUPING(o.o9) = 0 AND MAX(c.viewdepth) + 1 >= 9))
   AND (GROUPING(o.o10) = 1 OR (Grouping(o.o10) = 0 
                            AND MAX(c.viewdepth) + 1 >= 10))
   AND (GROUPING(o.o11) = 1 OR (GROUPING(o.o11) = 0
                            AND MAX(c.viewdepth) + 1 >= 11))
   AND (GROUPING(o.o12) = 1 OR (GROUPING(o.o12) = 0
                            AND MAX(c.viewdepth) + 1 >= 12))
ORDER BY COUNT(e.EMPLOYID)

The trick is in the HAVING, the lines

(GROUPING(o.o4) = 1 OR (GROUPING(o.o4) = 0 AND MAX(c.viewdepth) + 1 >= 4))

remove all the rows returned by the ROLLUP that aren't grouped (Grouping(o.o4) = 1) or that are at a lower depth than the max wanted (GROUPING(o.o4) = 0 AND MAX(c.viewdepth) + 1 >= 4)).

SQLFiddle demo

SQL Server :Recursive CTE? Creating Report From an Organisation Structure

2 Answers2