Row data to columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Row data to columns

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-09-13 : 17:44:41

I have 3 tables as follows:

declare @Employee table (
ID int,
Name varchar(20)
)

insert into @Employee (
ID,
Name
)
values
(1, 'Bob'),
(2, 'Doug'),
(3, 'Bill')

declare @AbsenceType table (
ID int,
Descrip varchar(50)
)

insert into @AbsenceType (
ID,
Descrip
)
values
(1, 'Flex'),
(2, 'Vacation')

declare @AbsenceHistory table (
ID int,
EmployeeId int,
StartDate date,
EndDate date,
SpansWeekend bit,
AbsType int
)

insert into @AbsenceHistory (
ID,
EmployeeId,
StartDate,
EndDate,
SpansWeekend,
AbsType
)
values
(1, 1, '2011-07-01','2011-07-04', 0, 2),
(2, 1, '2011-08-25','2011-08-29', 0, 2),
(3, 1, '2011-06-01','2011-06-01', 0, 1),
(4, 2, '2011-08-01','2011-08-04', 0, 2),
(5, 2, '2011-09-04','2011-09-06', 0, 1);

I am trying to query the data so that the data is returned as follows:

Name Vacation Flex
Bob 40 8
Doug 32 16

I am getting close with the query below, but can't figure out how to get the data in a single row like the example above:

WITH TEMP AS
(
SELECT
E.Name,
AH.StartDate, AH.EndDate, AH.SpansWeekend, AH.AbsType,
CASE WHEN AH.SpansWeekend = 'True'
THEN
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * 8)
ELSE
(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1) -(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2)
-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1
ELSE 0 END)-(CASE WHEN DATENAME(dw, AH.EndDate)
= 'Saturday' THEN 1 ELSE 0 END)) * 8) END AS Hours
FROM
@Employee E,
@AbsenceHistory AH
WHERE
E.ID IN ('1','2') AND
E.ID = AH.EmployeeID AND
(AH.StartDate >= '2011-01-01' AND AH.EndDate <= '2011-10-31') AND
AH.AbsType IN (1,2)
)
SELECT
TMP.Name,
AT.Descrip,
SUM(TMP.Hours) AS Hours
FROM
TEMP TMP,
@AbsenceType AT
WHERE
TMP.AbsType = AT.ID
GROUP BY
TMP.Name, AT.Descrip

Thanks in advance.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-13 : 22:03:02

This should do it. You can also use Pivot, but this will work in SQL Server 2000 as well:

Just replace your last select statement with this:

[CODE]
SELECT
TMP.Name,

SUM(CASE WHEN AT.Descrip='Vacation' THEN Hours ELSE 0 END) AS 'Vacation',
SUM(CASE WHEN AT.DESCRIP='Flex' THEN HOURS ELSE 0 END) AS 'Flex'

FROM
TEMP TMP,
@AbsenceType AT
WHERE
TMP.AbsType = AT.ID
GROUP BY
TMP.Name

Name Vacation Flex
-------------------- ----------- -----------
Bob 40 8
Doug 32 16

(2 row(s) affected)
[/CODE]

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-09-14 : 12:14:42

Works great!!
Thanks flamblaster, I really appreciate your help!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-14 : 12:26:32

Welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources