CODEWITHSUNDEEP
sql-server-2005
user1354197
user1354197

Reputation: 21

Events Table rows To Columns Using CTE?

I have hunted for two days looking for what I need to no avail. I am sure this is pathetically simple as well, but this is not my bag.

The table I have stores an EmployeeID, and TimeStamp. I need to Select every other row, such that the Odd rows and even rows map to new columns Like this:

INPUT Table TimePunches:

EmployeeID     Time
1              08:00:00
1              12:00:00
1              12:30:00
1              17:00:00

Ouput Table:

Employee       TimeIn      TimeOut
1             08:00:00     12:00:00
1             12:30:00     17:00:00

I will calc time from each output row after I figure out how to transform the rows to exactly two columns. I realize this would output garbage if it doesn't return an even number of rows, but I want to get that far first ;)

Upvotes: 2

Views: 360

Answers (4)

Andriy M
Andriy M

Reputation: 77677

Another solution involving ROW_NUMBER() and, for a change, PIVOT:

WITH prepared AS (
  SELECT
    EmployeeID,
    Time,
    TimeRow  = (ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY Time) - 1) / 2
    TimeKind =
      CASE (ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY Time) - 1) % 2
        WHEN 0 THEN 'TimeIn'
        WHEN 1 THEN 'TimeOut'
      END
  FROM TimePunches
)
SELECT
  EmployeeID,
  TimeIn,
  TimeOut
FROM prepared
PIVOT (
  MAX(Time) FOR TimeKind IN (TimeIn, TimeOut)
) p
ORDER BY
  EmployeeID,
  TimeRow

You can play with this query on SQL Fiddle.

Upvotes: 1

therealmitchconnors
therealmitchconnors

Reputation: 2760

For the record, this table structure needs to be changed, but it is possible to do what you want. This query makes me feel all kinds of dirty, and I would never consider using it in my code, but it will work, so consider yourself warned.

with rank1 as (
SELECT EmployeeID, Time, RANK() OVER (PARTITION BY EmployeeID ORDER BY Time) as rank
FROM TimePunches),
rank2 as (
SELECT EmployeeID, Time, RANK() OVER (PARTITION BY EmployeeID ORDER BY Time) as rank
FROM TimePunches)

SELECT rank1.EmployeeID, rank1.Time as timeIn, rank2.Time as timeOut
FROM rank1
JOIN rank2 on rank1.EmployeeID = rank2.EmployeeID AND rank1.rank % 2 = 1 AND rank1.rank - (rank1.rank % 2) = rank2.rank

Upvotes: 0

GSerg
GSerg

Reputation: 78175

with prepared_data as (
  select
    EmployeeID,
    Time,
    row_number() over(partition by EmployeeID order by Time) as num
  from TimePunches
)
select
  p1.EmployeeID,
  p1.Time as TimeIn,
  p2.Time as TimeOut
from
  prepared_data p1
  left join prepared_data p2 on p1.EmployeeID = p2.EmployeeID and p1.num + 1 = p2.num
where
  p1.num % 2 = 1
order by
  p1.EmployeeID,
  p1.num

Upvotes: 0

aF.
aF.

Reputation: 66697

One solution might be, in pseudo-code:

declare @timeIn datetime, @timeOut datetime
declare @emp int

get one row at a time {

if (row is even)
  @timeIn = Time from row
}
else {
  @timeOut = Time from row
  @emp = Employee from row
  insert into #tempTable @emp, @timeIn, @timeOut
}

select #tempTable

To get a row at a time you can use ROW_NUMBER or cursors.

Upvotes: 0

Related Questions