Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert Column into rows depending on Date. Expand / Collapse
Author
Message
Posted Thursday, May 30, 2013 10:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:53 PM
Points: 213, Visits: 535
Hi All,
Please suggest me an idea on the below scenario.

I am having table which contain studentid,studentname,startdate,enddate. Sample data is as below.
StudentID StudName StartDate EndDate
1 Mike 4/8/2013 6/16/2013
1 Mike 6/18/2013 8/26/2013
2 John 1/29/2014 4/8/2014
3 Andy 4/10/2014 6/18/2014

Now on depending StartDate and EndDate I want to convert data as below.i.e Monthwise.

StudentID StudName Month
1 Mike Apr
1 Mike May
1 Mike Jun
1 Mike Jul
1 Mike Aug
2 John Jan
2 John Feb
2 John Mar
2 John Apr
3 Andy Apr
3 Andy May
3 Andy Jun



Thanks
Abhas.

Post #1458517
Posted Friday, May 31, 2013 8:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 13,007, Visits: 12,422
You really need to post ddl and sample data. I posted it for you this time.

You can use a tally table here to fill in the "missing" months. You can read about tally tables here. http://www.sqlservercentral.com/articles/62867/

This produces the output you want.

set dateformat mdy

if OBJECT_ID('tempdb..#Student') is not null
drop table #Student

create table #Student
(
StudentID int,
StudentName char(4),
StartDate datetime,
EndDate datetime
)

insert #Student
select 1, 'Mike', '4/8/2013', '6/16/2013' union all
select 1, 'Mike', '6/18/2013', '8/26/2013' union all
select 2, 'John', '1/29/2014', '4/8/2014' union all
select 3, 'Andy', '4/10/2014', '6/18/2014'

select StudentID, StudentName, left(DATENAME(month, DATEADD(month, N - 1, 0)), 3) as [Month]
from #Student s
join Tally t on t.N >= month(StartDate) and t.N <= month(enddate)
group by StudentID, StudentName, left(DATENAME(month, DATEADD(month, N - 1, 0)), 3), DATEADD(month, N - 1, 0)
order by StudentID, DATEADD(month, N - 1, 0)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1458733
Posted Saturday, June 8, 2013 11:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
micheltomes (6/8/2013)
I think my friend , gave you right script. So not need to give more details. Please check the code and use it. I think will some all problem.


Spam reported.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1461328
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse