SQL打印矩阵(转圈)

--1.定义变量
DECLARE @I INT=0
DECLARE @J INT=1
DECLARE @Z INT=0
DECLARE @N INT= 10
DECLARE @Flag INT = @N

DECLARE @Number INT
DECLARE @Where NVARCHAR(4000)
DECLARE @Sql NVARCHAR(4000)

--2.产生N*N的数据
SELECT number,0 AS I,0 AS J,0 AS Z INTO #Tmp FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND @N*@N
ORDER BY number desc 

--3.拼条件
SET @Where=STUFF( (SELECT ','+QUOTENAME(number) FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND @N
FOR XML PATH('')),1,1,'')

--4.这里最重要
UPDATE TOP(@Flag )#Tmp SET z=@Flag WHERE z=0
SET @Flag = @Flag -1
WHILE @Flag>0
BEGIN
	UPDATE TOP (@Flag*2) #Tmp SET z=@Flag WHERE z=0
	SET @Flag=@Flag-1
END  

--5.更新坐标
SET @Flag=1
SET @N=1
DECLARE Cur CURSOR FOR 
SELECT number,Z FROM #Tmp ORDER BY number ASC

OPEN Cur
FETCH NEXT FROM Cur INTO @number,@Z
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @Flag=1
		SET @I=@I+1
	ELSE IF @Flag=2
		SET @J=@J+1
	ELSE IF @Flag=3
		SET @I=@I-1
	ELSE IF @Flag=4
		SET @J=@J-1
	 
	SET @N=@N+1
	IF (@Z<@N)
	BEGIN 
		SET @Flag=@Flag+1
		SET @N=1
	END
	IF @Flag>4
		SET @Flag=1
UPDATE #Tmp SET i=@i,j=@j WHERE number=@number
FETCH NEXT FROM Cur INTO @number,@Z
END 
CLOSE Cur
DEALLOCATE Cur

--6.行列转换
SET @sql='SELECT '+@where+' FROM (SELECT number,i,j  FROM #Tmp) Q
PIVOT 
(
MAX(number)
FOR j IN('+@where+')
)P'

EXEC(@sql)

--7.删除表
DROP TABLE #Tmp