0

Hi I have a problem with regarding on split/pivot dates

Here is my query

select Name
     , Start
     , End 
  from Employees 
 where Start >= '1/27/2014' 
   and End <= '1/31/2014'

And Sample Data would be like this

enter image description here

What I want to do is to split/pivot all data by date range like this

enter image description here


But I don't know how can i do this and if it is possible?
Thank you.

user1647667
  • 1,269
  • 4
  • 14
  • 26
  • It looks like that is pivoting and not splitting. If yes, then please change your title and tag list to show pivoting. Pivoting means converting a column to a row and vice versa. – Erran Morad Feb 05 '14 at 05:41
  • It is more unpivot then pivot;) – Devart Feb 05 '14 at 09:47
  • What's the type of `Start` and `End`? If it's a timestamp, you probably want [a look at this](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). Note that this still applies to MySQL anyways, given that timestamps can have differing sets of millisecond precision. – Clockwork-Muse Feb 05 '14 at 10:53
  • A good solution here: [Get a list of dates between two dates using a function](http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function). – Andriy M Feb 11 '14 at 14:41

2 Answers2

0

First, create the table as - 

USE `Test`;
CREATE  TABLE `test`.`Tbl_Sample_Data` (
  `Name` VARCHAR(50) NULL ,    
  `Start` DATETIME NULL ,
  `End` DATETIME NULL );

Insert rows using - 

INSERT INTO `test`.`tbl_sample_data`
(`End`,
`Name`,
`Start`)
VALUES
(
'PutEndDateHere',
'PutNameHere',
'PutStartDateHere'
);

Here, dates should be in yyyy-MM-dd format, otherwise you get error 1292.

Use this query to get what you want.

SELECT `Name`,'Start' AS colName, `Start` AS value FROM test.tbl_sample_data
UNION ALL
SELECT `Name`,'End' AS colName, `End` AS value FROM test.tbl_sample_data
ORDER BY `Name` ASC

Note that column names whose name is the same as a keyword must be enclosed in backticks. Eg. Example Name. Otherwise, you get error.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Erran Morad
  • 4,563
  • 10
  • 43
  • 72
0

you need to generate all the dates. i did this here by using a cte. i then join this date range with your data and receive the sought result.

with DateRange AS
(
    SELECT CAST('1/27/2014' as DATEtime) DateValue
     UNION ALL
    SELECT dateadd(dd,1,DateValue)
      FROM DateRange
     WHERE dateadd(dd,1,DateValue) <= CAST('3/31/2014' as datetime)
)
select name
     , DateValue
  from Employees
  join DateRange
    on start <= DateValue
   and [end] >= datevalue
 order by 
       name
     , DateValue

outdated after your updated question i would go about it with a simple union: 
select Name
     , Start
  from Employees 
 where Start >= '1/27/2014' 
   and End <= '1/31/2014'
 union all
select Name
     , End
  from Employees 
 where Start >= '1/27/2014' 
   and End <= '1/31/2014'
 order by
       Name
Brett Schneider
  • 3,993
  • 2
  • 16
  • 33