April 13, 2012 at 4:48 pm
Hello All,
I have the following table...
DECLARE @EmploymentHistory TABLE (
CompanyName VARCHAR (100), EmployeeName VARCHAR (100),
EmploymentBeginDate DateTime, EmploymentEndDate DateTime)
INSERT @EmploymentHistory (CompanyName, EmployeeName, EmploymentBeginDate, EmploymentEndDate)
SELECT 'Jack and Jill Company', 'John Doe', '1/2/2001', '7/28/2010'
UNION
SELECT 'Jack and Jill Company', 'Jill Smith', '4/12/2004', NULL
UNION
SELECT 'Jack and Jill Company', 'Smart Alex', '9/27/2003', '11/16/2011'
SELECT * FROM @EmploymentHistory
The output will be as shown in the attachment "ActualResults.jpg"
I am looking to get the following output (shown in the attachment "DesiredResults.jpg") from my query by possibly pivoting it?
Could you please help me out with an efficient way to do this in one query?
Regards,
RexHelios
April 14, 2012 at 12:15 am
RexHelios (4/13/2012)
Hello All,I have the following table...
DECLARE @EmploymentHistory TABLE (
CompanyName VARCHAR (100), EmployeeName VARCHAR (100),
EmploymentBeginDate DateTime, EmploymentEndDate DateTime)
INSERT @EmploymentHistory (CompanyName, EmployeeName, EmploymentBeginDate, EmploymentEndDate)
SELECT 'Jack and Jill Company', 'John Doe', '1/2/2001', '7/28/2010'
UNION
SELECT 'Jack and Jill Company', 'Jill Smith', '4/12/2004', NULL
UNION
SELECT 'Jack and Jill Company', 'Smart Alex', '9/27/2003', '11/16/2011'
SELECT * FROM @EmploymentHistory
The output will be as shown in the attachment "ActualResults.jpg"
I am looking to get the following output (shown in the attachment "DesiredResults.jpg") from my query by possibly pivoting it?
Could you please help me out with an efficient way to do this in one query?
Regards,
RexHelios
You can use pivot command in your T-sql query. Try this query-
select companyname,
substring([John Doe],1,charindex([John Doe],'-') as [JohnDoeemploymentbegindate],
substring([John Doe],charindex([John Doe],'-', len([john doe]) as [JohnDoeemploymentenddate],
substring([Jim smith],1,charindex([Jim smith],'-') as [JimSmithemployment begin date],
substring([Jim smith],charindex([Jim smith],'-', len([Jim smith]) as [Jimsmithemploymentenddate],
substring([Smart Alex],1,charindex([Smart Alex],'-') as [SmartAlexemploymentbegindate],
substring([Smart Alex],charindex([Smart Alex],'-', len([Smart Alex]) as [SmartAlexemploymentenddate]
from @employmenthistory
pivot
(
min( EmploymentBeginDate+'-'+ EmploymentEndDate) for employeename in ([John Doe],[Jim smith],[Smart Alex]) P
group by companyname
--Divya
April 14, 2012 at 4:37 am
Divya Agrawal (4/14/2012)
RexHelios (4/13/2012)
Hello All,I have the following table...
DECLARE @EmploymentHistory TABLE (
CompanyName VARCHAR (100), EmployeeName VARCHAR (100),
EmploymentBeginDate DateTime, EmploymentEndDate DateTime)
INSERT @EmploymentHistory (CompanyName, EmployeeName, EmploymentBeginDate, EmploymentEndDate)
SELECT 'Jack and Jill Company', 'John Doe', '1/2/2001', '7/28/2010'
UNION
SELECT 'Jack and Jill Company', 'Jill Smith', '4/12/2004', NULL
UNION
SELECT 'Jack and Jill Company', 'Smart Alex', '9/27/2003', '11/16/2011'
SELECT * FROM @EmploymentHistory
The output will be as shown in the attachment "ActualResults.jpg"
I am looking to get the following output (shown in the attachment "DesiredResults.jpg") from my query by possibly pivoting it?
Could you please help me out with an efficient way to do this in one query?
Regards,
RexHelios
You can use pivot command in your T-sql query. Try this query-
select companyname,
substring([John Doe],1,charindex([John Doe],'-') as [JohnDoeemploymentbegindate],
substring([John Doe],charindex([John Doe],'-', len([john doe]) as [JohnDoeemploymentenddate],
substring([Jim smith],1,charindex([Jim smith],'-') as [JimSmithemployment begin date],
substring([Jim smith],charindex([Jim smith],'-', len([Jim smith]) as [Jimsmithemploymentenddate],
substring([Smart Alex],1,charindex([Smart Alex],'-') as [SmartAlexemploymentbegindate],
substring([Smart Alex],charindex([Smart Alex],'-', len([Smart Alex]) as [SmartAlexemploymentenddate]
from @employmenthistory
pivot
(
min( EmploymentBeginDate+'-'+ EmploymentEndDate) for employeename in ([John Doe],[Jim smith],[Smart Alex]) P
group by companyname
Did you try it on the sample data the OP provided?
Doesn't seem to work when I try it.
April 14, 2012 at 5:07 am
Two demonstrations as below.
DECLARE @EmploymentHistory TABLE (
CompanyName VARCHAR (100), EmployeeName VARCHAR (100),
EmploymentBeginDate DateTime, EmploymentEndDate DateTime)
INSERT @EmploymentHistory (CompanyName, EmployeeName, EmploymentBeginDate, EmploymentEndDate)
SELECT 'Jack and Jill Company', 'John Doe', '1/2/2001', '7/28/2010'
UNION
SELECT 'Jack and Jill Company', 'Jill Smith', '4/12/2004', NULL
UNION
SELECT 'Jack and Jill Company', 'Smart Alex', '9/27/2003', '11/16/2011'
-- Demonstration with pivot using a UNION statement
select *
from
(
select CompanyName , EmployeeName + ' Start' as ColName, EmploymentBeginDate as Dated
from @EmploymentHistory as E
UNION
select CompanyName , EmployeeName + ' End' as ColName, EmploymentEndDate
from @EmploymentHistory as E
) as DataReady
pivot
(max(Dated) for ColName IN
([Jill Smith End],
[Jill Smith Start],
[John Doe Start],
[John Doe End],
[Smart Alex Start],
[Smart Alex End])
) as Pivotted
-- Fixed version of query suggested by Divya
select companyname,
substring([John Doe],1,charindex('-',[John Doe])-1) as [JohnDoeemploymentbegindate],
substring([John Doe],charindex('-',[John Doe])+1, len([john doe])) as [JohnDoeemploymentenddate],
substring([Jill smith],1,charindex('-',[Jill smith])-1) as [JillSmithemployment begin date],
substring([Jill smith],charindex('-',[Jill smith])+1, len([Jill smith])) as [Jillsmithemploymentenddate],
substring([Smart Alex],1,charindex('-',[Smart Alex])-1) as [SmartAlexemploymentbegindate],
substring([Smart Alex],charindex('-',[Smart Alex])+1, len([Smart Alex])) as [SmartAlexemploymentenddate]
from
(select CompanyName, EmployeeName,
isnull(convert(varchar(12),EmploymentBeginDate,103),'')
+'-'+
isnull(convert(varchar(12),EmploymentEndDate,103),'') as EmpHistory from @EmploymentHistory) as DataReady
pivot
(min(EmpHistory) for employeename in ([John Doe],[Jill smith],[Smart Alex])) as P
Fitz
April 16, 2012 at 3:03 am
vinu512 (4/14/2012)
Divya Agrawal (4/14/2012)
RexHelios (4/13/2012)
Hello All,I have the following table...
DECLARE @EmploymentHistory TABLE (
CompanyName VARCHAR (100), EmployeeName VARCHAR (100),
EmploymentBeginDate DateTime, EmploymentEndDate DateTime)
INSERT @EmploymentHistory (CompanyName, EmployeeName, EmploymentBeginDate, EmploymentEndDate)
SELECT 'Jack and Jill Company', 'John Doe', '1/2/2001', '7/28/2010'
UNION
SELECT 'Jack and Jill Company', 'Jill Smith', '4/12/2004', NULL
UNION
SELECT 'Jack and Jill Company', 'Smart Alex', '9/27/2003', '11/16/2011'
SELECT * FROM @EmploymentHistory
The output will be as shown in the attachment "ActualResults.jpg"
I am looking to get the following output (shown in the attachment "DesiredResults.jpg") from my query by possibly pivoting it?
Could you please help me out with an efficient way to do this in one query?
Regards,
RexHelios
You can use pivot command in your T-sql query. Try this query-
select companyname,
substring([John Doe],1,charindex([John Doe],'-') as [JohnDoeemploymentbegindate],
substring([John Doe],charindex([John Doe],'-', len([john doe]) as [JohnDoeemploymentenddate],
substring([Jim smith],1,charindex([Jim smith],'-') as [JimSmithemployment begin date],
substring([Jim smith],charindex([Jim smith],'-', len([Jim smith]) as [Jimsmithemploymentenddate],
substring([Smart Alex],1,charindex([Smart Alex],'-') as [SmartAlexemploymentbegindate],
substring([Smart Alex],charindex([Smart Alex],'-', len([Smart Alex]) as [SmartAlexemploymentenddate]
from @employmenthistory
pivot
(
min( EmploymentBeginDate+'-'+ EmploymentEndDate) for employeename in ([John Doe],[Jim smith],[Smart Alex]) P
group by companyname
Did you try it on the sample data the OP provided?
Doesn't seem to work when I try it.
Hey, i have just posted the query to give you a brief idea....it need some repair to make it work.. find below the workable solution
DECLARE @EmploymentHistory TABLE (
CompanyName VARCHAR (100), EmployeeName VARCHAR (100),
EmploymentBeginDate DateTime, EmploymentEndDate DateTime)
INSERT @EmploymentHistory (CompanyName, EmployeeName, EmploymentBeginDate, EmploymentEndDate)
SELECT 'Jack and Jill Company', 'John Doe', '1/2/2001', '7/28/2010'
UNION
SELECT 'Jack and Jill Company', 'Jill Smith', '4/12/2004', NULL
UNION
SELECT 'Jack and Jill Company', 'Smart Alex', '9/27/2003', '11/16/2011'
select companyname,cast(EmploymentBeginDate as varchar) +'-'+ cast(EmploymentEndDate as varchar) empdate,EmployeeName
from @employmenthistory
select companyname,
substring([John Doe],1,charindex('-',[John Doe])-1) as [JohnDoeemploymentbegindate],
substring([John Doe],charindex('-',[John Doe])+1, len([john doe])) as [JohnDoeemploymentenddate],
substring([Jim smith],1,charindex('-',[Jim smith])-1) as [JimSmithemployment begin date],
substring([Jim smith],charindex('-',[Jim smith])+1, len([Jim smith])) as [Jimsmithemploymentenddate],
substring([Smart Alex],1,charindex('-',[Smart Alex])-1) as [SmartAlexemploymentbegindate],
substring([Smart Alex],charindex('-',[Smart Alex])+1, len([Smart Alex])) as [SmartAlexemploymentenddate]
from
(select companyname,cast(EmploymentBeginDate as varchar) +'-'+ cast(EmploymentEndDate as varchar) empdate, EmployeeName
from @employmenthistory)t
pivot
(min(empdate) for employeename in ([John Doe],[Jim smith],[Smart Alex])) P
--Divya
April 16, 2012 at 4:19 am
Sorry Divya, Jill and Jim seems to have been confused in the code selected. (Jill during the inserts, Jim during the CTE).;-)
Fitz
April 17, 2012 at 9:32 am
Hello All,
Thanks a lot to everyone that replied with detailed inputs and suggestions to help me find a solution. I really appreciate your help.
Sincerely,
Rex
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply