July 12, 2010 at 6:34 am
Hey All,
I've given several replies to my friends from this forum, that's the time to help me .. hehehehe!!
The point is, I have a table with a code identity, name and datetime tracking field..
The current Example is:
id name Date
1 John 12/06/2010 06:00
2 John 12/06/2010 12:00
3 John 12/06/2010 13:00
4 John 12/06/2010 18:00
5 John 13/06/2010 06:00
6 John 13/06/2010 14:00
----Missing time
7 John 13/06/2010 18:00
I want to separate just four columns according to the same date, in the example below, columns within june,12, after columns within June,13 and so on .....
It sounds simple, but the problem is, in the example of June,13 I have just three trackings, it's missing one ... how can I build my pivot filling this blank....
I want this!!
id name Date1 date2 date3 date4
1 John 12/06/2010 06:00 12/06/2010 12:00 12/06/2010 13:00 12/06/2010 18:00
2 John 13/06/2010 06:00 13/06/2010 14:00 NULL 13/06/2010 18:00
Can anyone help me??
Many thanks!!
July 12, 2010 at 9:27 am
1. How do you determine that it's missing, and that it belongs there?
2. You might want to read the article in the first link in my signature. Doing what it suggests will help you get answers faster.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 12, 2010 at 10:18 am
Assuming that you have access to the names from a separate table, and that the times are static, e.g. if there's a time in the morning, it will always be at 6:00 then this will be done automatically by the pivot operator.
http://msdn.microsoft.com/en-US/library/ms177410%28v=SQL.90%29.aspx
Here's an example fragment from a report I recently worked on:
SELECT *
FROM (SELECT p.sequenceStart, d.data, d.sequenceNumber - p.sequenceStart+1 AS rowNumber
FROM pages p
LEFT JOIN data d ON rg.sequenceNumber >= p.sequenceStart
AND p.sequenceStart + 5 > rg.sequenceNumber
) r
PIVOT (
MAX(r.data)
FOR r.rowNumber IN ([1], [2], [3], [4], [5]))
) AS wr
In that case pages and data were CTEs that I used to stage the data in a format I could use. In particular, the sequence number was a key for the datavalue, so I used max to return the unaltered data.
You can't use a datetime value as a pivot column natively, so I'd suggest using the hour as the column, and the date value as the data.
I hope that helps...
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
July 12, 2010 at 2:36 pm
I second Wayne: there is nothing provided to support the missing row needs to be between 6 and 7. It could be between 5 and 6, too (assuming the time will always be limited to be between 06:00 and 18:00).
Therefore, it's impossible to define where to place the NULL value.
Once this issue is resolved I would use the CrossTab approach referenced in my signature, probably together with ROW_NUMBER() to name the four columns.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply