Split a row into multiple rows based on column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split a row into multiple rows based on column

Author

Topic

seplor
Starting Member

2 Posts

Posted - 2014-09-05 : 16:22:57

I'm need help to split a row into multiple rows based on multiple column values.

time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00

I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.

row 1
time_in 10:00
time_out 12:00

row 2
time_in 13:00
time_out 14:00

row 3
time_in 15:00
time_out 16:00

Any help would be appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-06 : 06:40:11

[code];with cte as
(
select time_id, employee_id, [time] = time_in from [table] union all
select time_id, employee_id, [time] = time_out from [table] union all
select time_id, employee_id, [time] = break1_in from [table] union all
select time_id, employee_id, [time] = break1_out from [table] union all
select time_id, employee_id, [time] = break2_in from [table] union all
select time_id, employee_id, [time] = break2_out from [table]
),
cte2 as
(
select *, rn = row_number() over (partition by time_id, employee_id order by [time])
from cte
)
select time_id, employee_id, time_in = min([time]), time_out = max([time])
from cte2
group by time_id, employee_id, (rn - 1)/ 2[/code]

KH
[spoiler]Time is always against us[/spoiler]

seplor
Starting Member

2 Posts

Posted - 2014-09-08 : 12:50:34

Thanks.

This has solved my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources