split one row with date into many

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

SQL Server 2012 Forums

Transact-SQL (2012)

split one row with date into many

Author

Topic

birtprofi
Starting Member

7 Posts

Posted - 2013-07-04 : 10:30:30

Hi guys, I need your help.

I have a table that contains vacation data from | til. for example:

ID | Name | From | Til
1 | xy | 2013-07-01 | 2013-07-20 |
2 | ab | 2013-06-15 | 2013-06-25 |

For a reporting tool I need for every "ID" a single row. Like this:

ID | Name | Date
1 | xy | 2013-07-01
1 | xy | 2013-07-02
1 | xy | 2013-07-03
....

So please give me your advices. Is this possible with sql?

best regards
rf

rf

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-04 : 11:19:57

[CODE]

DECLARE @Temp TABLE (ID INT, NAME VARCHAR(10), [From] DATE, [Til] DATE);

INSERT INTO @Temp VALUES
(1, 'xy', '2013-07-01', '2013-07-20'),
(2, 'ab', '2013-06-15', '2013-06-25');

SELECT T.ID, T.Name, DATEADD(dd, number, [From]) as [DATE]
FROM @Temp T, master..spt_values where type = 'P' and
number <= DATEDIFF(dd, [From], [Til]);

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 12:24:33

If you've a calendar table you can utilize that too instead of spt_values
I generally dont prefer using system objects like spt_value especially in production. even if no calendar table is present, I usually create a tally table for this purpose as in Jeffs article

http://www.sqlservercentral.com/articles/T-SQL/62867/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-04 : 12:36:47

If you don't have permissions to create tables in the database, another option is to use a CTE to create a tally table on the fly: http://blogs.inkeysolutions.com/2011/05/creating-tally-tables-using-cte-in-sql.html

birtprofi
Starting Member

7 Posts

Posted - 2013-07-08 : 03:33:03

HI guy´s

thank you very much for your help. Your suggestions and solutions works perfect for me.

best regards
rafael

rf

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 03:34:30

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources