Get Nested/Hierarchical Data from SQL Table

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

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

StackExchange Database Administrators Q#176619, answer score: 2