SqlServer单行时间拆分多行展示

请各位大神帮忙看下这个问题怎么处理、表设计无法改变、只能按照这个表格式进行处理。青锋在手、谁能一键屠龙

我只知道需要用到sql行转列

提供个粗浅的笨方法吧：

第一步：按时间分组排序,生成table1

select 

     前三个分组字段，

     row_number () over (partition by dw_date order by dw_date desc) as rn 

from  table 

第二步：

select 

      3个分组字段，

     min(a.日期) ,  --开始日期

     max(case when f.rn is not null then f.日期

                      when e.rn is not null then e.日期

                      when d.rn is not null then d.日期

                      when c.rn is not null then c.日期

                      when b.rn is not null then b.日期 

                       else a.日期 end
                   ) ,  --结束日期

     max(case when f.rn is not null then 6

                      when e.rn is not null then 5

                      when d.rn is not null then 4

                      when c.rn is not null then 3

                      when b.rn is not null then 2 else 1 end
                   )  分钟

from table1 a 

left join table1 b on a.rn=b.rn-1 and b.时间-a.时间<90s

left join table1 c on b.rn=c.rn-1 and c.时间-b.时间<90s

left join table1 d on c.rn=d.rn-1 and d.时间-c.时间<90s

left join table1 e on d.rn=e.rn-1 and e.时间-d.时间<90s

left join table1 f  on e.rn=f.rn-1 and f.时间-e.时间<90s

。

。

。

left join ....  --一般这种不会有很多连续的，确认一个最大值，写那些个就够了

group by 3个分组字段

第一步忘调了，应该是升序

select 

     前三个分组字段，

     row_number () over (partition by 前三个分组字段 order by 日期 asc) as rn 

from  table 

;with T1 as 
(
    select line_id,MACHINE_ID,MACHINE_NAME,ROW_NUMBER() OVER(PARTITION BY  line_id,MACHINE_ID,MACHINE_NAME ORDER BY [Time] DESC) AS rowId,[Time] from T 
),
T2 as
(select a.line_id,a.MACHINE_ID,a.MACHINE_NAME,DATEDIFF(SECOND,a.[Time],isnull(b.[Time],a.[Time])) as DiffData,a.[Time],isnull(b.[Time],GETDATE()) as endData from T1 as a
left join T1 as b on a.line_id=b.line_id and a.MACHINE_ID=b.MACHINE_ID and a.MACHINE_NAME=b.MACHINE_NAME and b.rowId=a.rowId+1),
T3 as
(
    select line_id,MACHINE_ID,MACHINE_NAME,ROW_NUMBER() OVER(PARTITION BY  line_id,MACHINE_ID,MACHINE_NAME ORDER BY endData DESC) AS rowId2,endData from T2 where DiffData>=90
)

select * from
(
--有间断
select c.line_id,c.MACHINE_ID,c.MACHINE_NAME,min(c.[Time]) as Start_Time,max(c.[Time]) as End_Time,count(c.[Time]) as 持续分钟 from T3 as a
left join T3  as b on a.line_id=b.line_id and a.MACHINE_ID=b.MACHINE_ID and a.MACHINE_NAME=b.MACHINE_NAME and a.rowId2=b.rowId2-1
left join T2 as c on a.line_id=c.line_id and a.MACHINE_ID=c.MACHINE_ID and a.MACHINE_NAME=c.MACHINE_NAME and a. a.endData<c.[Time] and ISNULL(b.endData,GETDATE())>c.[Time] 
group by c.line_id,c.MACHINE_ID,c.MACHINE_NAME,a.endData,isnull(b.endData,'')

union all
--无间断
select a.line_id,a.MACHINE_ID,a.MACHINE_NAME,min(a.[Time]) as Start_Time,max(a.[Time]) as End_Time,count(a.[Time]) as 持续分钟 from T2 as a
left join T3 as b on a.line_id=b.line_id and a.MACHINE_ID=b.MACHINE_ID and a.MACHINE_NAME=b.MACHINE_NAME
where b.line_id is null
group by a.line_id,a.MACHINE_ID,a.MACHINE_NAME
) as t
order by t.line_id,t.MACHINE_ID,t.MACHINE_NAME,t.Start_Time
 

;with T1 as 
(
    select line_id,MACHINE_ID,MACHINE_NAME,ROW_NUMBER() OVER(PARTITION BY  line_id,MACHINE_ID,MACHINE_NAME ORDER BY [Time]) AS rowId,[Time] from T 
),
T2 as
(select a.line_id,a.MACHINE_ID,a.MACHINE_NAME,DATEDIFF(SECOND,a.[Time],isnull(b.[Time],a.[Time])) as DiffData,a.[Time],isnull(b.[Time],GETDATE()) as endData from T1 as a
left join T1 as b on a.line_id=b.line_id and a.MACHINE_ID=b.MACHINE_ID and a.MACHINE_NAME=b.MACHINE_NAME and b.rowId=a.rowId+1),
T3 as
(
    select line_id,MACHINE_ID,MACHINE_NAME,ROW_NUMBER() OVER(PARTITION BY  line_id,MACHINE_ID,MACHINE_NAME ORDER BY endData DESC) AS rowId2,endData from T2 where DiffData>=90
)
select * from
(
--有间断
select c.line_id,c.MACHINE_ID,c.MACHINE_NAME,min(c.[Time]) as Start_Time,max(c.[Time]) as End_Time,count(c.[Time]) as 持续分钟 from T3 as a
left join T3  as b on a.line_id=b.line_id and a.MACHINE_ID=b.MACHINE_ID and a.MACHINE_NAME=b.MACHINE_NAME and a.rowId2=b.rowId2-1
left join T2 as c on a.line_id=c.line_id and a.MACHINE_ID=c.MACHINE_ID and a.MACHINE_NAME=c.MACHINE_NAME and a. a.endData<c.[Time] and ISNULL(b.endData,GETDATE())>c.[Time] 
group by c.line_id,c.MACHINE_ID,c.MACHINE_NAME,a.endData,isnull(b.endData,'')
union all
--无间断
select a.line_id,a.MACHINE_ID,a.MACHINE_NAME,min(a.[Time]) as Start_Time,max(a.[Time]) as End_Time,count(a.[Time]) as 持续分钟 from T2 as a
left join T3 as b on a.line_id=b.line_id and a.MACHINE_ID=b.MACHINE_ID and a.MACHINE_NAME=b.MACHINE_NAME
where b.line_id is null
group by a.line_id,a.MACHINE_ID,a.MACHINE_NAME
) as t
order by t.line_id,t.MACHINE_ID,t.MACHINE_NAME,t.Start_Time

--盲打也许会有所调整