3

I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'.

The TagAssignment table stores the relationship between a Tag and a date. So a tag can be mapped to a single date(period).

I want to output a nested hierarchy from sql.

SQL Tables:

-- tag assigned to a specific date
CREATE TABLE [dbo].[TagAssignment](
    [TagAssignmentID] [int] IDENTITY(1,1) NOT NULL,
    [TagID] [int] NOT NULL,
    [Period] [date] NOT NULL
);

-- task(s) mapped to tag(s)
CREATE TABLE [dbo].[TagTaskMapping](
    [TagID] [int] NOT NULL,
    [TaskID] [int] NOT NULL
);

-- tag table
CREATE TABLE [dbo].[Tag](
    [TagID] [int] IDENTITY(1,1) NOT NULL,
    [TagName] [nvarchar](150) NOT NULL
)

TagAssignment mapping table data:

TagAssignmentID TagID   Period
24                3    31/05/2017
14                2    31/05/2017

TagTaskMapping table data:

TagID   TaskID
  2       1
  2       2
  2       3
  3       1
  3       3

Here is my query...

DECLARE @Period datetime = '2017-05-31'    
;WITH CTE_TagAssignment
AS
(
    -- GET TAG(S) Assigned to selected PERIOD
    SELECT 
         ta.TagID
        ,t.TagName
        ,null as 'Task'
    FROM dbo.TagAssignment ta
    INNER JOIN 
        dbo.Tag t
         ON t.TagID = ta.TagID
    WHERE ta.Period = @Period

    UNION ALL

    /**USING RECURSION!!!!**/
    -- foreach above tag assigned to a period, get it's associated task(s)
    SELECT 
        ttm.TagID
        ,null AS 'TagName'
        ,ttm.TaskID as 'Task'
    FROM CTE_TagAssignment cta
    INNER JOIN 
        dbo.TagTaskMapping ttm
        ON cta.TagID = ttm.TagID
)  
SELECT *
FROM CTE_TagAssignment  
OPTION (MAXRECURSION 100);

However, I get this error: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

This is the hierarchy output I would like...

TagID   TagName   Task
2       Level 5 
                   1
                   2
                   3
3       Level 3 
                   1
                   3
  • Set maxrecursion to 0. – Erik Darling Jun 19 '17 at 11:23
  • I tries this but my query is wrong somewhere. Runs an infinite loop. – K09 Jun 19 '17 at 12:10
2

IMHO you don't need a recursive solution, you can get it by using a simple JOIN.

DECLARE @Period datetime = '20170531';

SELECT     t.TagID, t.TagName, tm.TaskID
FROM       TagAssignment ta
INNER JOIN TagTaskMapping tm
ON         tm.TagID = ta.TagID
INNER JOIN Tag t
ON         t.TagID = tm.TagID
WHERE      ta.Period = @period
ORDER BY   tm.TagID, tm.TaskID;
GO
TagID | TagName | TaskID
----: | :------ | -----:
    2 | Level 5 |      1
    2 | Level 5 |      2
    2 | Level 5 |      3
    3 | Level 3 |      1
    3 | Level 3 |      3

dbfiddle here

2

NOTE: This is basically a variation on McNets' solution, with the formatting you requested, and an explanation of the issue with your CTE.

As McNets notes, you don't actually have a recursive query. In a recursive CTE, the recursive part of the query normally has some condition on it that forces it to stop; it eventually encounters a set of values that generate no rows. Yours ahs no such stop condition; it's just going to keep generating the new rows repeatedly.

Your real problem is a formatting issue.

This should get the results you want, in the format you want:

SELECT CASE WHEN Header = 1 THEN CAST(TagID as varchar(20)) ELSE '' END as TagID
      ,CASE WHEN Header = 1 THEN TagName ELSE '' END as TagName
      ,CASE WHEN Header = 1 THEN '' ELSE CAST(TaskId as varchar(20)) END as TaskID
  FROM (
        SELECT DISTINCT
               t.TagID, t.TagName, CAST(NULL as int) as TaskID, 1 as Header
          FROM TagAssignment ta
                 INNER JOIN TagTaskMapping tm ON ta.TagID = tm.TagID
                 INNER JOIN Tag t ON ta.TagID = t.TagID
         WHERE ta.Period = @period
        UNION ALL
        SELECT t.TagID, t.TagName, tm.TaskID, 0 as Header
          FROM TagAssignment ta
                 INNER JOIN TagTaskMapping tm ON ta.TagID = tm.TagID
                 INNER JOIN Tag t ON ta.TagID = t.TagID
         WHERE ta.Period = @period
       ) sq
 ORDER BY sq.TagID, Header DESC, sq.TaskID;

Check the dbfiddle (again, based on the work done by McNets).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.