master..spt_values

master..spt_values是要匯出資料的表，spt_values是在master資料庫下，所以是master..spt_values。

用法舉例

獲取時間段內的每一天

WITH DateSequence AS
 (SELECT CAST('2024-11-01' AS DATE) AS DateValue
 UNION ALL
SELECT DATEADD(DAY, 1, DateValue)FROM DateSequence 
WHERE DATEADD(DAY, 1, DateValue) <= CAST('2024-11-10' AS DATE)
)
SELECT DateValue FROM DateSequence 
OPTION (MAXRECURSION 0);--- 0 代表不限制遞迴次數。 為防止死迴圈，sqlserver預設遞迴100次

--方法二

DECLARE @StartDate DATETIME = '2024/11/01'
DECLARE @EndDate DATETIME ='2024/11/10'

SELECT CONVERT (VARCHAR (100),dateadd(DAY,n.number,@StartDate),23) AS every_time,
CONVERT (VARCHAR (100),dateadd(DAY,n.number,@StartDate),112) AS every_time112
FROM master..spt_values n WHERE type = 'p'AND n.number <= DATEDIFF(day, @StartDate, @EndDate);

用法二： 巧用行列轉換之 列轉行

SELECT Number,
T.col1,t.Col2,t.Col3,t.Col4 -- 為了便於觀察與原表區別，此處所有列都要
, RIGHT(LEFT(T.col4,Number-1),
CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
FROM
master..spt_values, table1 T
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
(SUBSTRING(T.col4,Number,1) = ','
OR SUBSTRING(T.col4,Number,1) = '') --this does not work correctly anyway