行转列疑难问题

[smilyvm]

日期 时间 巡检器 巡检点
2017-12-12 04:45:38 52822 61A01
2017-12-12 04:46:44 52822 61A02
2017-12-12 04:47:13 52822 61A03
2017-12-12 04:48:10 52822 61A04
2017-12-12 04:49:00 52822 61A05
2017-12-12 04:49:55 52822 61A06
2017-12-12 04:50:24 52822 61A07
2017-12-12 04:50:50 52822 61A01
2017-12-12 04:51:20 52822 61A02
2017-12-12 04:52:17 52822 61A03
2017-12-12 04:52:56 52822 61A04
2017-12-12 04:53:23 52822 61A05
2017-12-12 05:11:08 52822 61A06
2017-12-12 05:11:35 52822 61A07
2017-12-12 05:12:02 52822 61A01
2017-12-12 05:12:36 52822 61A02
2017-12-12 05:13:39 52822 61A03
2017-12-12 05:14:07 52822 61A04
2017-12-12 05:17:05 52822 61A05
2017-12-12 05:18:00 52822 61A06
2017-12-12 05:18:55 52822 61A07
2017-12-12 05:19:25 52822 61A01
2017-12-12 05:19:55 52822 61A02
2017-12-12 05:20:25 52822 61A03
2017-12-12 05:21:23 52822 61A04
2017-12-12 05:22:02 52822 61A05
2017-12-12 05:22:28 52822 61A06
2017-12-12 14:17:07 52822 61A07

上表是原始记录，需要得到下表格式
日期 巡检器 61A01 61A02 61A03 61A04 61A05 61A06 61A07
2017-12-12 52822 04:45:38 04:46:44 04:47:13 04:48:10 04:49:00 04:49:55 04:50:24
2017-12-12 52822 04:50:50 04:51:20 04:52:17 04:52:56 04:53:23 05:11:08 05:11:35
2017-12-12 52822 05:12:02 05:12:36 05:13:39 05:14:07 05:17:05 05:18:00 05:18:55
2017-12-12 52822 05:19:25 05:19:55 05:20:25 05:21:23 05:22:02 05:22:28 14:17:07

各位有什么好方法不？？

[smilyvm]

[二月十六]

https://blog.csdn.net/sinat_28984567/article/details/79865046

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([日期] Date,[时间] NVARCHAR(100),[巡检器] int,[巡检点] nvarchar(25))
Insert #T
select '2017-12-12','04:45:38',52822,N'61A01' union all
select '2017-12-12','04:46:44',52822,N'61A02' union all
select '2017-12-12','04:47:13',52822,N'61A03' union all
select '2017-12-12','04:48:10',52822,N'61A04' union all
select '2017-12-12','04:49:00',52822,N'61A05' union all
select '2017-12-12','04:49:55',52822,N'61A06' union all
select '2017-12-12','04:50:24',52822,N'61A07' union all
select '2017-12-12','04:50:50',52822,N'61A01' union all
select '2017-12-12','04:51:20',52822,N'61A02' union all
select '2017-12-12','04:52:17',52822,N'61A03' union all
select '2017-12-12','04:52:56',52822,N'61A04' union all
select '2017-12-12','04:53:23',52822,N'61A05' union all
select '2017-12-12','05:11:08',52822,N'61A06' union all
select '2017-12-12','05:11:35',52822,N'61A07' union all
select '2017-12-12','05:12:02',52822,N'61A01' union all
select '2017-12-12','05:12:36',52822,N'61A02' union all
select '2017-12-12','05:13:39',52822,N'61A03' union all
select '2017-12-12','05:14:07',52822,N'61A04' union all
select '2017-12-12','05:17:05',52822,N'61A05' union all
select '2017-12-12','05:18:00',52822,N'61A06' union all
select '2017-12-12','05:18:55',52822,N'61A07' union all
select '2017-12-12','05:19:25',52822,N'61A01' union all
select '2017-12-12','05:19:55',52822,N'61A02' union all
select '2017-12-12','05:20:25',52822,N'61A03' union all
select '2017-12-12','05:21:23',52822,N'61A04' union all
select '2017-12-12','05:22:02',52822,N'61A05' union all
select '2017-12-12','05:22:28',52822,N'61A06' union all
select '2017-12-12','14:17:07',52822,N'61A07'
Go
--测试数据结束

DECLARE @sql VARCHAR(MAX)  
SET @sql = '
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 日期,巡检器,巡检点 ORDER BY 时间) rn FROM #T
)
select 日期,巡检器'  
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 日期,巡检器,巡检点 ORDER BY 时间) rn FROM #T
)
SELECT  @sql = @sql + ',max(case 巡检点 when ''' + 巡检点  
        + ''' then 时间 else null end)[' + 巡检点 + ']'  
FROM    ( SELECT DISTINCT  
                    巡检点  
          FROM      cte  
        ) a  
SET @sql = @sql  
    + ' from cte group by 日期,巡检器,rn'  
EXEC(@sql)  

[wwfxgm]

引用 3 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([日期] Date,[时间] NVARCHAR(100),[巡检器] int,[巡检点] nvarchar(25))
Insert #T
select '2017-12-12','04:45:38',52822,N'61A01' union all
select '2017-12-12','04:46:44',52822,N'61A02' union all
select '2017-12-12','04:47:13',52822,N'61A03' union all
select '2017-12-12','04:48:10',52822,N'61A04' union all
select '2017-12-12','04:49:00',52822,N'61A05' union all
select '2017-12-12','04:49:55',52822,N'61A06' union all
select '2017-12-12','04:50:24',52822,N'61A07' union all
select '2017-12-12','04:50:50',52822,N'61A01' union all
select '2017-12-12','04:51:20',52822,N'61A02' union all
select '2017-12-12','04:52:17',52822,N'61A03' union all
select '2017-12-12','04:52:56',52822,N'61A04' union all
select '2017-12-12','04:53:23',52822,N'61A05' union all
select '2017-12-12','05:11:08',52822,N'61A06' union all
select '2017-12-12','05:11:35',52822,N'61A07' union all
select '2017-12-12','05:12:02',52822,N'61A01' union all
select '2017-12-12','05:12:36',52822,N'61A02' union all
select '2017-12-12','05:13:39',52822,N'61A03' union all
select '2017-12-12','05:14:07',52822,N'61A04' union all
select '2017-12-12','05:17:05',52822,N'61A05' union all
select '2017-12-12','05:18:00',52822,N'61A06' union all
select '2017-12-12','05:18:55',52822,N'61A07' union all
select '2017-12-12','05:19:25',52822,N'61A01' union all
select '2017-12-12','05:19:55',52822,N'61A02' union all
select '2017-12-12','05:20:25',52822,N'61A03' union all
select '2017-12-12','05:21:23',52822,N'61A04' union all
select '2017-12-12','05:22:02',52822,N'61A05' union all
select '2017-12-12','05:22:28',52822,N'61A06' union all
select '2017-12-12','14:17:07',52822,N'61A07'
Go
--测试数据结束

DECLARE @sql VARCHAR(MAX)  
SET @sql = '
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 日期,巡检器,巡检点 ORDER BY 时间) rn FROM #T
)
select 日期,巡检器'  
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 日期,巡检器,巡检点 ORDER BY 时间) rn FROM #T
)
SELECT  @sql = @sql + ',max(case 巡检点 when ''' + 巡检点  
        + ''' then 时间 else null end)[' + 巡检点 + ']'  
FROM    ( SELECT DISTINCT  
                    巡检点  
          FROM      cte  
        ) a  
SET @sql = @sql  
    + ' from cte group by 日期,巡检器,rn'  
EXEC(@sql)  

版主。我在 ssms中运行报错了；

[wwfxgm]

(28 行受影响)
消息 102，级别 15，状态 1，第 38 行
“?”附近有语法错误。
消息 102，级别 15，状态 1，第 40 行
“?”附近有语法错误。

[二月十六]

引用 5 楼 wwfxgm 的回复:

(28 行受影响)
消息 102，级别 15，状态 1，第 38 行
“?”附近有语法错误。
消息 102，级别 15，状态 1，第 40 行
“?”附近有语法错误。

截张图看看。
不过现在的CSDN好像上传不了图片了，我刚才执行了一下，没问题，截图没传上来

[wwfxgm]

引用 6 楼 sinat_28984567 的回复:

[quote=引用 5 楼 wwfxgm 的回复:]

(28 行受影响)
消息 102，级别 15，状态 1，第 38 行
“?”附近有语法错误。
消息 102，级别 15，状态 1，第 40 行
“?”附近有语法错误。

截张图看看。
不过现在的CSDN好像上传不了图片了，我刚才执行了一下，没问题，截图没传上来
[/quote]

一直上传不了图片。真是郁闷了。老是说有错误。

[二月十六]

引用 7 楼 wwfxgm 的回复:

[quote=引用 6 楼 sinat_28984567 的回复:]
[quote=引用 5 楼 wwfxgm 的回复:]

(28 行受影响)
消息 102，级别 15，状态 1，第 38 行
“?”附近有语法错误。
消息 102，级别 15，状态 1，第 40 行
“?”附近有语法错误。

截张图看看。
不过现在的CSDN好像上传不了图片了，我刚才执行了一下，没问题，截图没传上来
[/quote]

一直上传不了图片。真是郁闷了。老是说有错误。[/quote]
调试一下，把exec换成print试试

[wwfxgm]

引用 8 楼 sinat_28984567 的回复:

[quote=引用 7 楼 wwfxgm 的回复:]
[quote=引用 6 楼 sinat_28984567 的回复:]
[quote=引用 5 楼 wwfxgm 的回复:]

(28 行受影响)
消息 102，级别 15，状态 1，第 38 行
“?”附近有语法错误。
消息 102，级别 15，状态 1，第 40 行
“?”附近有语法错误。

截张图看看。
不过现在的CSDN好像上传不了图片了，我刚才执行了一下，没问题，截图没传上来
[/quote]

一直上传不了图片。真是郁闷了。老是说有错误。[/quote]
调试一下，把exec换成print试试[/quote]


;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 日期,巡?器,巡?? ORDER BY ??) rn FROM #T
)
select 日期,巡?器,max(case 巡?? when '61A01' then ?? else null end)[61A01],max(case 巡?? when '61A02' then ?? else null end)[61A02],max(case 巡?? when '61A03' then ?? else null end)[61A03],max(case 巡?? when '61A04' then ?? else null end)[61A04],max(case 巡?? when '61A05' then ?? else null end)[61A05],max(case 巡?? when '61A06' then ?? else null end)[61A06],max(case 巡?? when '61A07' then ?? else null end)[61A07] from cte group by 日期,巡?器,rn

[wwfxgm]

运行结果这么会出现 问号。

[二月十六]

引用 10 楼 wwfxgm 的回复:

运行结果这么会出现 问号。

中文问题，把字段名称改成英文的试试