Convert row to column using Pivot without any conditions
3 answers
At least I wouldn't use pivot for this, in my opinion it's easier to do it with group by and row_number:
select UserId, max(starttime) as starttime, max(endtime) as endtime
from (
select UserId,
case when StartOrEnd = 'S' then time end as starttime,
case when StartOrEnd = 'E' then time end as endtime,
row_number() over (partition by UserID order by time asc)
+ case when StartOrEnd = 'S' then 1 else 0 end as GRP
from table1
) X
group by UserId, GRP
order by starttime
The resulting table splits the time into start / end time columns (to handle cases where there is only one) and uses a row number trick to group the S / E items together. The outer selection simply groups the rows into a single row.
Example in SQL Fiddle
+4
source to share
Ineffective solution like JamesZ but should work
create table #tst (userid int,start_end char(1),times datetime)
insert #tst values
(1,'S','07-27-2015 16:45'),
(1,'E','07-27-2015 16:46'),
(2,'S','07-27-2015 16:47'),
(2,'E','07-27-2015 16:48'),
(1,'S','07-27-2015 16:49'),
(1,'E','07-27-2015 16:50')
WITH cte
AS (SELECT Row_number()OVER(ORDER BY times) rn,*
FROM #tst),
cte1
AS (SELECT a.userid,
a.start_end,
a.times,
CASE WHEN a.userid = b.userid THEN 0 ELSE 1 END AS com,
a.rn
FROM cte a
LEFT OUTER JOIN cte b
ON a.rn = b.rn + 1),
cte2
AS (SELECT userid,
start_end,
times,
(SELECT Sum(com)
FROM cte1 b
WHERE b.rn <= a.rn) AS row_num
FROM cte1 a)
SELECT USERID,
starttime=Min(CASE WHEN start_end = 's' THEN times END),
endtime=Max(CASE WHEN start_end = 'e' THEN times END)
FROM cte2
GROUP BY USERID,
row_num
0
source to share
Here is another method
declare @t table(userid int, StartOrEnd char(1), time datetime)
insert into @t
select 1,'S','2015-07-27 16:45' union all
select 1,'E','2015-07-27 16:46' union all
select 2,'S','2015-07-27 16:47' union all
select 2,'E','2015-07-27 16:48' union all
select 1,'S','2015-07-27 16:49' union all
select 1,'E','2015-07-27 16:50'
select userid,min(time) as minimum_time, max(time) as maximum_time from
(
select *, row_number() over (partition by cast(UserID as varchar(10))
+StartOrEnd order by time asc) as sno
from @t
) as t
group by userid,sno
Result
userid minimum_time maximum_time
----------- ----------------------- -----------------------
1 2015-07-27 16:45:00.000 2015-07-27 16:46:00.000
2 2015-07-27 16:47:00.000 2015-07-27 16:48:00.000
1 2015-07-27 16:49:00.000 2015-07-27 16:50:00.000
0
source to share