question

sapabin avatar image
sapabin asked

Split one row into multiple row (monthwise) based on date

My table1 data is like this Datefield Field1 Field2 Field2 Field4 01-01-2012 ABC 10 15 17 01-04-2013 PQR 7 80 65 01-05-2014 XYZ 15 25 77 I want the row of first date will copy month wise upto previous month of next date and so on. The output would look something like this Month/Year Field1 Field2 Field2 Field4 01/2012 ABC 10 15 17 02/2012 ABC 10 15 17 03/2012 ABC 10 15 17 04/2012 ABC 10 15 17 ..... 03/2013 ABC 10 15 17 04/2013 PQR 7 80 65 05/2013 PQR 7 80 65 06/2013 PQR 7 80 65 07/2013 PQR 7 80 65 ....... 04/2014 PQR 7 80 65 05/2014 XYZ 15 25 77 Kindly reply with sql query/stored procedure or Ms Access query to do the above job done.
sql-server-2008sql-server-2008-r2accessconditional-split
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
GPO avatar image
GPO answered
This works on SQL 2012, but the LEAD() windowing function doesn't exist in SQL 2008 (the version that you've tagged). If you want a solution that also works in SQL 2008 let me know and I'll adjust. It will be a matter of doing an extra self join to mimic LEAD(). --Create and populate example table SELECT cast('20120101' as date) as datefield ,cast('ABC' as char(3)) as field1 ,cast('10' as smallint) as field2 ,cast('15' as smallint) as field3 ,cast('17' as smallint) as field4 INTO #table1 UNION ALL SELECT '20130401','PQR','7','80','65' UNION ALL SELECT '20140501','XYZ','15','25','77'; --Create a calendar table (mths) that covers the number of months in question --First determine the number of rows required declare @start as date = (SELECT min(datefield) FROM #table1); declare @end as date = (SELECT max(datefield) FROM #table1); declare @n as int = datediff(mm,@start,@end); with mths as ( SELECT TOP (@n) dateadd(mm,row_number() OVER(ORDER BY (SELECT NULL)),@start) as month_year FROM sys.messages ) , dates_with_next as ( SELECT t1.datefield ,t1.field1 ,t1.field2 ,t1.field3 ,t1.field4 ,isnull(lead(t1.datefield) over(order by t1.datefield),'99990101') as next_datefield FROM #table1 t1 ) SELECT mths.month_year ,dwn.field1 ,dwn.field2 ,dwn.field3 ,dwn.field4 FROM dates_with_next dwn JOIN mths ON dwn.datefield mths.month_year;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.