SQL query for splitting the period of date into each date for corresponding employee id

I have one table with name "Employee"...It has 3 fields:

1. empid
2. from date
3. todate

I want to split the period of date (开发者_StackOverflow社区dates between from date & to date including both) for the corresponding emp id.

For example here's the table I've got:

emp id        from date          to date
  1           1/1/2011           3/1/2011
  2           5/1/2011           5/1/2011

From that I'm want to get this:

1         1/1/2011
1         2/1/2011
1         3/1/2011
2         5/1/2011

What's an SQL query that would do this for me?

You wants... I gives...

;with Employee([emp id],[from date],[to date]) as (
    select 1, convert(datetime,'20110101'), convert(datetime,'20110103') union all
    select 2, '20010105', '20110105'
)

-- your query below here
select [emp id], [from date] + w.number*1000 + v.number [the date]
from Employee e
inner join master..spt_values w
    on w.type='P' and w.number <= datediff(d, [from date], [to date]) % 1000
inner join master..spt_values v
    on v.type='P'
       and v.number <= 999
       and (w.number*1000) + v.number <= datediff(d, [from date], [to date])
order by [emp id], [the date]

Here's a simpler version if you don't ever deal with more than 6 years at a time

select [emp id], [from date] + v.number [the date]
from Employee e
inner join master..spt_values v
    on v.type='P'
       and v.number <= datediff(d, [from date], [to date])
order by [emp id], [the date]

A solution that uses a recursive CTE (works with SQL Server 2005+):

WITH Employee (emp_id, from_date, to_date) AS (
  /* this is just a sample data definition */
  SELECT 1, CAST('20110101' AS datetime), CAST('20110103' AS datetime) UNION ALL
  SELECT 2, CAST('20110105' AS datetime), CAST('20110105' AS datetime)
),
unrolled AS (
  /* and this is the recursive CTE */
  SELECT
    emp_id,
    from_date AS date
  FROM Employee
  UNION ALL
  SELECT
    r.emp_id,
    DATEADD(day, 1, r.date)
  FROM Employee e
    INNER JOIN unrolled r
      ON e.emp_id = r.emp_id AND e.to_date > r.date
)
SELECT * FROM unrolled
ORDER BY emp_id, date