跪求大佬，这个效果怎么实现？

[nimeide1234567890]

id自增列
mac_bh设备编号nvarchar(10)
rq日期datetime
kssj开始时间datetime
jssj结束时间datetime

表脚本：
CREATE TABLE [dbo].[aaa] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[mac_bh] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[rq] [datetime] NULL ,
[kssj] [datetime] NULL ,
[jssj] [datetime] NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[aaa] WITH NOCHECK ADD
CONSTRAINT [PK_aaa] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
实现效果图：


横着从每个月1号到31号
竖着从每天的0点到晚上24点
显示的内容是开始时间+结束时间，能把设备号显示进去更好

[RINK_1]

为啥开始时间是10:00:00的被归到“9点-10点”这一档

[nimeide1234567890]

因为是10点开始的

[RINK_1]

引用 2 楼 luojianfeng 的回复:

因为是10点开始的

那不是应该归到10-11点这时间段里吗，否则和8点开始而被归到8-9点这个时间段矛盾了吗 试试下面的

DECLARE @DATE VARCHAR(10)
DECLARE @SQL VARCHAR(8000)

SET @DATE='2019-12'

;WITH CTE
AS
(SELECT NUMBER,DATEADD(DAY,NUMBER,CAST(@DATE+'-01' AS DATE)) AS SINGLE_DAY
FROM MASTER.DBO.SPT_VALUES 
WHERE TYPE='P'
AND NUMBER BETWEEN 0 AND DATEDIFF(DAY,CAST(@DATE+'-01' AS DATE),DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@DATE+'-01' AS DATE)))))

SELECT @SQL=ISNULL(@SQL+',','')+'['+CAST(SINGLE_DAY AS VARCHAR(10))+']' 
FROM CTE

SET @SQL='WITH CTE
          AS
		  (SELECT A.NUMBER,B.*,CAST(A.NUMBER AS VARCHAR)+''-''+CAST(A.NUMBER+1 AS VARCHAR) AS TIME_RANGE
           FROM MASTER.DBO.SPT_VALUES A
           LEFT JOIN
		   (SELECT RQ ,DATEPART(HOUR,kssj) AS START_HOUR,
		   MAX(CONTENT) AS CONTENT
		   FROM
		   (SELECT *,
		   STUFF((SELECT '',''+mac_bh+'' ''+CAST(kssj AS VARCHAR(30))+'' ''+CAST(jssj AS VARCHAR(30)) FROM #T WHERE A.RQ=RQ AND DATEPART(HOUR,KSSJ)=DATEPART(HOUR,A.KSSJ) FOR XML PATH('''')),1,1,'''') AS CONTENT
		   FROM #T AS A) AS B
		   GROUP BY RQ,DATEPART(HOUR,kssj)) AS B ON A.NUMBER=B.START_HOUR
		   WHERE TYPE=''P'' AND NUMBER<12)
		   
		   SELECT TIME_RANGE,'+@SQL+' FROM CTE
		   PIVOT (MAX(CONTENT) FOR RQ IN('+@SQL+')) B
		   ORDER BY NUMBER'

EXEC(@SQL)

[nimeide1234567890]

服务器: 消息 156，级别 15，状态 1，行 6
在关键字 'WITH' 附近有语法错误。

我的数据库是SQL SERVER 2000

[RINK_1]

引用 4 楼 luojianfeng 的回复:

服务器: 消息 156，级别 15，状态 1，行 6 在关键字 'WITH' 附近有语法错误。 我的数据库是SQL SERVER 2000

2000下，其它还好办，不过拼接字符串就比较麻烦，要写函数了。

[nimeide1234567890]

SQL对我来说很难，函数就是难上加难，你能帮我写出函数来吗？
重谢

[RINK_1]

引用 6 楼 luojianfeng 的回复:

SQL对我来说很难，函数就是难上加难，你能帮我写出函数来吗？ 重谢

函数

CREATE FUNCTION KSSJ_COMBINE
(@RQ DATE,@KSSJ DATETIME)
RETURNS VARCHAR(8000)

AS 

BEGIN

DECLARE @CONTENT VARCHAR(8000)

SELECT @CONTENT=ISNULL(@CONTENT+',','')+ ','+mac_bh+' '+CAST(kssj AS VARCHAR(30))+' '+CAST(jssj AS VARCHAR(30))
FROM 
(SELECT mac_bh,RQ,kssj,jssj FROM T1 GROUP BY mac_bh,RQ,kssj,jssj) AS A
WHERE RQ=@RQ AND DATEPART(HOUR,KSSJ)=DATEPART(HOUR,@KSSJ)

RETURN @CONTENT

END

调用函数

DECLARE @DATE VARCHAR(10)
DECLARE @SQL VARCHAR(8000)

SET @DATE='2019-12'

SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN RQ='''+CAST(SINGLE_DAY AS VARCHAR(10))+'''THEN CONTENT ELSE '''' END) AS '''+CAST(SINGLE_DAY AS VARCHAR(10))+''''
FROM 
((SELECT NUMBER,DATEADD(DAY,NUMBER,CAST(@DATE+'-01' AS DATE)) AS SINGLE_DAY
FROM MASTER.DBO.SPT_VALUES 
WHERE TYPE='P'
AND NUMBER BETWEEN 0 AND DATEDIFF(DAY,CAST(@DATE+'-01' AS DATE),DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@DATE+'-01' AS DATE)))))) AS A


SET @SQL=' SELECT A.NUMBER,B.*,CAST(A.NUMBER AS VARCHAR)+''-''+CAST(A.NUMBER+1 AS VARCHAR) AS TIME_RANGE
           INTO #A
           FROM MASTER.DBO.SPT_VALUES A
           LEFT JOIN
		   (SELECT RQ ,DATEPART(HOUR,kssj) AS START_HOUR,
		   MAX(DBO.KSSJ_COMBINE(RQ,KSSJ)) AS CONTENT
		   FROM T1
		   GROUP BY RQ,DATEPART(HOUR,kssj)) AS B ON A.NUMBER=B.START_HOUR
		   WHERE TYPE=''P'' AND NUMBER<12
		   
		   SELECT TIME_RANGE,'+@SQL+' 
		   FROM #A 
		   GROUP BY TIME_RANGE,NUMBER
		   ORDER BY NUMBER'

EXEC(@SQL)