Pivot Query Problem - 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

Database Design and Application Architecture

Pivot Query Problem

Author

Topic

ele
Starting Member

1 Post

Posted - 2008-06-27 : 20:49:15

Hi I have the following tables:

ScheduleHours - HourID(pk), EmpSchedID, WeekNumber, Day, Value
ScheduleModifiedHours - MofidiedID(pk), HourID(FK)
ScheduleLunches - LunchID(pk), HourID(FK)

Here is some sample data:

ScheduleHours:

HourID EmpSchedID, WeekNumber, Day, Value
-------------------------------------------
1 1 1 1 9-5
2 1 1 1 absent <- Related to Mod Table
3 1 1 1 10 <- related to lunch table
4 1 1 2 9-5
5 1 1 3 9-5
6 1 1 3 absent <- related to mod table
7 2 1 1 9-5

The Day field is used for the day of the week (1-7)

And I need to use pivoting to produce output that looks like this:

EmpSchedID WeekNumber Sun Mon Tue Wed Thu Fri Sat IsLunch
-----------------------------------------------------------------
1 1 absent 9-5 9-5 9-5 9-5 9-5 9-5 0
1 1 10 10 10 10 10 60 50 1
1 2 9-5 9-5 9-5 9-5 9-5 9-5 9-5 0

My main problem is the pivot function in 2005 requires an aggregate function but my value column doesn't need to be sumed or anything I just need it pivoted. Also I am unsure how to make the "Lunch" rows appear as described.

Here is what i have so far, any help would be appreciated.

SELECT SE.EmpSchedID, SE.[Name],PivotTable.WeekNumber, [1] AS 'Sun',[2] AS 'Mon',[3] AS 'Tue',[4] AS 'Wed',[5] AS 'Thu',[6] AS 'Fri',[7] AS 'Sat'
FROM
(SELECT SH.EmpSchedID, SH.WeekNumber, SH.[DAY], SH.[Value]
FROM ScheduleHours SH
JOIN ScheduleEmployees SE ON SH.EmpSchedID = SE.EmpSchedID
JOIN ScheduleCategories SC ON SE.SchedCatID = SC.SchedCatID
WHERE SC.SchedID = @SchedID
AND SH.HourID NOT IN (SELECT HourID FROM ScheduleModifiedHours)
AND NOT EXISTS (SELECT * FROM ScheduleLunches WHERE HourID = SH.HourID)
)
AS SOURCETABLE
PIVOT
(
max([value])
FOR [day] in ([1],[2],[3],[4],[5],[6],[7])
)
AS PIVOTTABLE
JOIN ScheduleEmployees SE ON PivotTable.EmpSchedID = SE.EmpSchedID

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 02:40:05

One thing i didnt understand was ypu've more than 1 value for Value field for a particular HourID EmpSchedID, WeekNumber, Day combination. So what should be value you will displaying for that day value once you pivot?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources