將結果輸出為 2 個單獨的列

所以我創建了一個計算字元串數組的平均值和中位數的程序：

CREATE PROCEDURE dbo.spAvg_Median 
   @input nvarchar(20) 
AS
BEGIN
   declare @split table(id int primary key identity (1,1), number int)

   INSERT INTO @split SELECT value FROM string_split(@input, ',')

   SELECT Average FROM (
       SELECT SUM(number)/COUNT(number) as [Average]
       FROM @split
       ) AS T1
   UNION
   SELECT Median FROM (
       SELECT o.number AS [Median], rn=ROW_NUMBER() OVER (ORDER BY o.number), c.c
       FROM @split AS o
       CROSS APPLY (SELECT c=COUNT(*) FROM @split b) AS c
       ) AS x
       WHERE rn IN ((c + 1)/2, (c + 2)/2)
END
RETURN 0 
GO

但是，當您執行此查詢時，您會得到以下結果：

EXEC dbo.spAvg_Median'1,5,9,8,7'

我希望它輸出的是一列中的平均值和旁邊另一列中的中值。

UNION 命令在那裡，所以它不會像這樣將它作為兩個不同的查詢輸出，因為這不是我想要的：

根據規則找到中位數（如果是偶數個值則為平均值，如果為奇數個值則為中間數）。可以這樣做。

CREATE PROCEDURE dbo.spAvg_Median 
   @input nvarchar(20) 
AS
BEGIN
   declare @split table(id int primary key identity (1,1), number int)
   declare @count int

   INSERT INTO @split SELECT value FROM string_split(@input, ',')
   select @count=count(*) from @split

       SELECT AVG(A.number) as [Average],B.Median
           FROM @split A
       CROSS APPLY
                   (
                   SELECT Median FROM (
                   SELECT
                             o.number AS [Median]
                           , rn=ROW_NUMBER() OVER (ORDER BY o.number ASC)
                   FROM    @split AS o        
                   ) AS x
                   WHERE rn =ROUND(@count/2.0,0)
                   ) B
       GROUP BY B.Median
END
RETURN 0 
GO

謝謝！

引用自：https://dba.stackexchange.com/questions/242398