Expand network using CTE without circular reference

Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference.
You can’t do that in a recursive CTE because you can only reference the CTE once in the recursive part. Then I thought about a “recursive csv string”. And I gave it a try.

Here is the result.

DECLARE @Stations TABLE
(
stationID INT,
name VARCHAR(255)
)

INSERT @Stations
SELECT 1, 'Glasgow' UNION ALL
SELECT 2, 'Edinburgh' UNION ALL
SELECT 3, 'York' UNION ALL
SELECT 4, 'London' UNION ALL
SELECT 5, 'Aberdeen' UNION ALL
SELECT 6, 'Bjuv'

DECLARE @Links TABLE
(
fromID INT,
toID INT
)

INSERT @Links
SELECT 1, 2 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 6, 4 UNION ALL
SELECT 5, 1

;WITH paths(stationIDs, pathLength, hopPath, fromID, fromName, toName)
AS (
SELECT CAST(stationID AS VARCHAR(MAX)),
CAST(-1 AS INT),
CAST(name AS VARCHAR(MAX)),
stationID,
CAST(name AS VARCHAR(MAX)),
CAST(NULL AS VARCHAR(MAX))
FROM @Stations

UNION ALL

SELECT p.stationIDs + '/' + CAST(l.toID AS VARCHAR(MAX)),
pathLength + 1,
p.hopPath + '-' + s.name,
l.toID,
p.fromName,
CAST(s.name AS VARCHAR(MAX))
FROM paths AS p
INNER JOIN @Links AS l ON l.fromID = p.fromID
INNER JOIN @Stations AS s ON s.stationID = l.toID
WHERE '/' + stationIDs + '/' NOT LIKE '%/' + CAST(l.toID AS VARCHAR(MAX)) + '/%'
)

SELECT fromName,
toName,
pathLength AS hops,
hopPath
FROM paths
WHERE pathLength >= 0
ORDER BY fromName,
toName,
pathLength
OPTION (MAXRECURSION 0)

If you only want the shortest path between two stations, replace the last SELECT statement with this statement.

SELECT fromName,
toName,
hops,
hopPath
FROM (
SELECT fromName,
toName,
pathLength AS hops,
hopPath,
ROW_NUMBER() OVER (PARTITION BY fromName, toName ORDER BY pathLength) AS recID
FROM paths
WHERE pathLength >= 0
) AS d
WHERE recID = 1
ORDER BY fromName,
toName
OPTION (MAXRECURSION 0)

SQL DATEADD Function Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate
Return Value = 2010-06-04 00:00:00.000


SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000


SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000


SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Week, 3, @DateNow) AS NewDate
Return Value = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000


SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000


SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000


SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:00.003

SQL DATEADD Function Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate
Return Value = 2010-06-04 00:00:00.000


SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000


SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000


SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Week, 3, @DateNow) AS NewDate
Return Value = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000


SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000


SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000


SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:00.003

Create a cross-tab (or "pivot") query in SQL

Cross-tab (or "pivot") query in SQL

You may find that your data is not well-suited for what you might term as pivot or cross-tab reports. Now, while I believe that such pivoting functionality belongs at the presentation tier and not the data tier, there are many ways to generate reports like this. Here is a simple example that takes normalized sales figures by year and quarter, and produces one row and four columns per year. Run this in Query Analyzer and the output should be pretty self-explanatory:

SET ANSI_WARNINGS OFF SET NOCOUNT ON CREATE TABLE dbo.SalesByQuarter ( Y INT, Q INT, sales INT, PRIMARY KEY (Y,Q) ) GO INSERT dbo.SalesByQuarter(Y,Q,Sales) SELECT 2003, 2, 479000 UNION SELECT 2003, 3, 321000 UNION SELECT 2003, 4, 324000 UNION SELECT 2004, 1, 612000 UNION SELECT 2004, 2, 524000 UNION SELECT 2004, 3, 342000 UNION SELECT 2004, 4, 357000 UNION SELECT 2005, 1, 734000 GO SELECT Y, Q1 = SUM(CASE WHEN Q=1 THEN Sales END), Q2 = SUM(CASE WHEN Q=2 THEN Sales END), Q3 = SUM(CASE WHEN Q=3 THEN Sales END), Q4 = SUM(CASE WHEN Q=4 THEN Sales END) FROM dbo.SalesByQuarter GROUP BY Y ORDER BY Y GO DROP TABLE dbo.SalesByQuarter GO

SQL Server 2005 introduces the new PIVOT keyword, however I think it will be a disappointment to most. What people have been searching for is a very dynamic way to pivot, so that you don't have to know all of the potential column headers beforehand. Again, I still feel that the best place to rotate this data is on the client. But I know that some people out there are stubborn and/or don't understand the difference.

In any case, here is the above query in SQL Server 2005 style:

CREATE TABLE dbo.SalesByQuarter ( Y INT, Q INT, sales INT, PRIMARY KEY (Y,Q) ) GO INSERT dbo.SalesByQuarter(Y,Q,Sales) SELECT 2003, 2, 479000 UNION SELECT 2003, 3, 321000 UNION SELECT 2003, 4, 324000 UNION SELECT 2004, 1, 612000 UNION SELECT 2004, 2, 524000 UNION SELECT 2004, 3, 342000 UNION SELECT 2004, 4, 357000 UNION SELECT 2005, 1, 734000 GO SELECT Y, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4 FROM (SELECT Y, Q, Sales FROM SalesByQuarter) s PIVOT ( SUM(Sales) FOR Q IN ([1],[2],[3],[4]) ) p ORDER BY [Y] GO DROP TABLE dbo.SalesByQuarter GO