SQL Server: How to Concatenate Parent Nodes in Irregular Category Trees

Consider this table:

id	ParentCategory	Name
1	NULL	Household
2	1	Furniture
3	1	Appliances
4	2	Chair
5	2	Couch
6	2	Bed
7	3	Refridgerator
8	3	Counter
10	3	Bathroom

We want each node to display all it's parents back to its root.

This SQL helps.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        bryanv
-- Create date: 11/28/2018
-- Description:    gets the whole list from here down
-- =============================================
CREATE FUNCTION dbo.CategoryFullName
(
    @CategoryID int
   
)
RETURNS nvarchar(200)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ans nvarchar(200)
    declare @parent int

    -- Add the T-SQL statements to compute the return value here
    select @ans=name, @parent=ParentCategory from Categories where id=@CategoryID

    if @parent is not null
    BEGIN
        set @ans=dbo.CategoryFullName(@parent)+' - '+@ans
    END


    -- Return the result of the function
    RETURN @ans

END
GO




This will look up the current category text, and prepend all parents recursively until it gets to the root.

 You can then use this in a computed column to autogenerate the entire category tree.

Now you can automatically get the full tree with a simple select...

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

SQL Server: Return All Dates in a Range

This function will return a list of all dates between a start date and an end date.





SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        bryan valencia
-- Create date: 11/20/2018
-- Description:    returns the dates between start and end (inclusive).
-- =============================================
CREATE FUNCTION DaysBetween(@startdate DATE, @enddate DATE)
RETURNS @calendar TABLE
(
    calendarday DATE PRIMARY KEY
)
AS
BEGIN
        -- Fill the table variable with the rows for your result set

    WITH calendar AS
    (
      SELECT CAST(@startdate AS DATETIME) DateValue
      UNION ALL
      SELECT  DateValue + 1
      FROM    calendar  
      WHERE   DateValue + 1 <= @enddate
    )

    insert into @calendar(calendarday)
    (
    SELECT  cast(DateValue as Date) calendarday
    FROM    calendar
    )
    OPTION (MAXRECURSION 0)
   

    RETURN
END
GO






To call it: SELECT * FROM [dbo].[DaysBetween] ('01 jan 2018', '01 feb 2018')







...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.