Results 1 to 7 of 7

Thread: Pivoting?

  1. #1
    Join Date
    Dec 2007
    Posts
    27

    Question Unanswered: Pivoting?

    I've got a single row of data with a bunch of datetimes from which I need to find the min and the max times, ignoring any NULLs. I'm thinking that pivoting the row and just using Min() and Max() to find the two would work. The problem is I've never really done a pivot, and I'm not sure how that would work. I Googled it, but most pages seem to assume that they are teaching you something that you already know (they're pretty unclear from what I can see).

    Let's say I have the following:

    Code:
    Create Table Events ( UserID int, Time1 datetime, Time2 datetime, Time3 datetime, Time4 datetime, Time5 datetime, Time6 datetime )
    
    INSERT INTO Events VALUES ( 4593, '12/4/2007 1:04 AM', '12/4/2007 2:53 AM', '12/4/2007 3:29 AM', '12/4/2007 5:59 PM', '12/4/2007 11:00 PM', '12/4/2007 2:05 AM' )
    INSERT INTO Events VALUES ( 1599, NULL, '12/4/2007 6:50 AM', '12/4/2007 12:03 PM', '12/4/2007 12:26 PM', NULL, '12/4/2007 2:57 pm' )
    INSERT INTO Events VALUES ( 7549, '12/4/2007 10:57 AM', '12/4/2007 3:57 PM', NULL )
    Now, if I were selecting from the first row that I entered, it should return '12/4/2007 1:04 AM' for min and '12/4/2007 2:05 AM' for max.

    From the second row it should return '12/4/2007 6:50 AM' for min and '12/4/2007 2:57 pm' for max

    Finally, from the last it should return '12/4/2007 10:57 AM' for min and '12/4/2007 3:57 PM' for max.

    You can see what I'm getting at (I hope). Is Pivoting the way to go for this? If not, what should I do? And finally, if so, how exactly do I pivot?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by VentureFree
    Is Pivoting the way to go for this? If not, what should I do?
    no, it isn't

    you should normalize your data so that instead of multiple columns called TimeN, you have multiple rows of a single Time column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    27
    you should normalize your data so that instead of multiple columns called TimeN, you have multiple rows of a single Time column
    Unfortunately that's not an option. I didn't create the DB, and I'm not allowed to alter the existing tables (too much is already built on the data as is). Believe me, I'd love to change it to make it easier to work with. I just have to make due with what's there, though.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your table is actually more like it has been pivoted - you are really looking to unpivot. Agreed with Rudy (first normal form - tut tut) but if you've inherited it and can't change things then you are stuck.

    Do you know UNION queries? I don't know if Rudy has a better idea but I would create a union query with a different date column in each select and use your MIN\ MAX on that.

    HTH

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    better idea? no

    the CASE expressions for the min and max here each involve some 25 lines of code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2007
    Posts
    27
    The following seems to work. Is there any reason that I shouldn't do it this way (besides the obvious "You shouldn't have to")?

    SELECT Min(Time1) as FirstTime, Max(Time1) as LastTime FROM
    (SELECT Time1 FROM Events
    UNION
    SELECT Time2 FROM Events
    UNION
    SELECT Time3 FROM Events
    UNION
    SELECT Time4 FROM Events
    UNION
    SELECT Time5 FROM Events
    UNION
    SELECT Time6 FROM Events)
    AS Times;

    Thanks for the Union suggestion. I'd never actually done one. Seems simple enough at first glance.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The only thing worth noting is most of the time it is better to use UNION ALL - it is more efficient. However, since you are aggregating it does not matter.

    Nope - that's exactly right

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •