Pivot or not to Pivot? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot or not to Pivot?

Author

Topic

tonyr1971
Starting Member

5 Posts

Posted - 2009-04-28 : 10:31:24

Hi All,

Hope someone can shed some light on my following query.

The problem is I have a table with the following columns:

FridgeID nvarchar(8)
ReadingDate datetime
Temperature real

I need to build a report that list all the fridge temperatures for each day of the month with each day no. across. But, the Fridge has a AM temperature reading and a PM temperature reading. That needs to populate in each day.

I wish to build a report with the following columns:

FridgeID|Time|Day 01|Day 02|Day 03|Day 04| ---> Day 31
--------------------------------------------------------
Raw Mat1|AM |2.5 |3.5 |2.5 |5.4
|PM |2.6 |2.4 |5.5 |6.4
--------------------------------------------------------

Is this possible? I have thought about pivot, can't work it out as we have both an AM and PM temperature reading, also thought about building a temp table and looping through each day.

Any thoughts.

Thanks in advance

Tony

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 10:50:33

SELECT FridgeID, CASE WHEN DatePart(hour, ReadingDate)>=12 THEN 'PM' ELSE 'AM' END TimeOfDay, ...
...
GROUP BY FridgeID, CASE WHEN DatePart(hour, ReadingDate)>=12 THEN 'PM' ELSE 'AM' END TimeOfDay

tonyr1971
Starting Member

5 Posts

Posted - 2009-04-28 : 11:27:59

Thanks robvolk, that gets the AM/PM problem sorted.

But how do I split each ReadingDate into a corresponding day no?

With your query

SELECT FridgeID, Temperature, ReadingDate, CASE WHEN DatePart(hour, ReadingDate)>=12 THEN 'PM' ELSE 'AM' END
FROM OBJECT_FridgeTemperature
GROUP BY FridgeID, Temperature, ReadingDate, CASE WHEN DatePart(hour, ReadingDate)>=12 THEN 'PM' ELSE 'AM' END

I get this data:

FG000106 2.5 2009-04-01 06:47:21.000 AM
FG000106 2.6 2009-04-01 17:16:51.000 AM
FG000106 3.5 2009-04-02 00:23:14.000 AM
FG000106 2.4 2009-04-02 14:20:02.000 AM
FG000106 2.5 2009-04-03 00:25:55.000 AM
FG000106 5.5 2009-04-03 08:24:36.000 AM
FG000106 5.4 2009-04-04 05:13:49.000 AM
FG000106 6.4 2009-04-04 13:16:26.000 PM

What I would like is the following format:

FridgeID|Time|Day 01|Day 02|Day 03|Day 04| ---> Day 31
--------------------------------------------------------
FG000106|AM |2.5 |3.5 |2.5 |5.4
|PM |2.6 |2.4 |5.5 |6.4
--------------------------------------------------------

Would a pivot do it? Been trying this idea. But it's throwing me!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 13:19:45

[code]select fridgeid, timeofday, [1] Day01, [2] Day02, [3] Day03, [4] Day04, [5] Day05, [6] Day06, [7] Day07,

Day08, [9] Day09, [10] Day10, [11] Day11, [12] Day12, [13] Day13, [14] Day14, [15] Day15,
[16] Day16, [17] Day17, [18] Day18, [19] Day19, [20] Day20, [21] Day21, [22] Day22, [23] Day23,
[24] Day24, [25] Day25, [26] Day26, [27] Day27, [28] Day28, [29] Day29, [30] Day30, [31] Day31
from (select fridgeid, temp, datepart(day, readingdate) [day], CASE WHEN DatePart(hour, ReadingDate)>=12 THEN 'PM' ELSE 'AM' END TimeOfDay
from OBJECT_FridgeTemperature) x
pivot (
max(temperature)
for [day] in ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12],[13],[14],[15]
,[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) as pvt
order by 1, 2[/code]

tonyr1971
Starting Member

5 Posts

Posted - 2009-04-29 : 03:17:38

Fantastic stuff robvolk, perfect and I have looked at your SQL code and understand it. Very clear!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources