行转列疑难问题

smilyvm 2018-06-15 03:09:54

日期时间巡检器巡检点
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

各位有什么好方法不？？

...全文

870 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六 2018-06-21

打赏
举报

引用 10 楼 wwfxgm 的回复:

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

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

wwfxgm 2018-06-21

打赏
举报

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

wwfxgm 2018-06-21

打赏
举报

引用 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

二月十六 2018-06-21

打赏
举报

引用 7 楼 wwfxgm 的回复:

[quote=引用 6 楼 sinat_28984567 的回复:]
[quote=引用 5 楼 wwfxgm 的回复:]
(28 行受影响)

消息 102，级别 15，状态 1，第 38 行

“?”附近有语法错误。

消息 102，级别 15，状态 1，第 40 行

“?”附近有语法错误。

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

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

wwfxgm 2018-06-21

打赏
举报

引用 6 楼 sinat_28984567 的回复:

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

(28 行受影响)

消息 102，级别 15，状态 1，第 38 行

“?”附近有语法错误。

消息 102，级别 15，状态 1，第 40 行

“?”附近有语法错误。

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

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

二月十六 2018-06-21

打赏
举报

引用 5 楼 wwfxgm 的回复:

(28 行受影响)

消息 102，级别 15，状态 1，第 38 行

“?”附近有语法错误。

消息 102，级别 15，状态 1，第 40 行

“?”附近有语法错误。

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

wwfxgm 2018-06-21

打赏
举报

(28 行受影响)

消息 102，级别 15，状态 1，第 38 行

“?”附近有语法错误。

消息 102，级别 15，状态 1，第 40 行

“?”附近有语法错误。

wwfxgm 2018-06-21

打赏
举报

引用 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中运行报错了；

二月十六 2018-06-15

打赏
举报

--测试数据

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)