Convert Column into rows depending on Date.

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  May 30, 2013 at 10:46 pm

  #273345

  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.

  StudentIDStudNameStartDateEndDate

  1 Mike 4/8/2013 6/16/2013

  1 Mike 6/18/20138/26/2013

  2 John 1/29/20144/8/2014

  3 Andy 4/10/20146/18/2014

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

  StudentIDStudNameMonth

  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.

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  May 31, 2013 at 8:43 am

  #1620115

  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/[/url]

  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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Jeff Moden

  SSC Guru

  Points: 1003857

  More actions

  June 8, 2013 at 11:26 pm

  #1622267

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)