Pivot Table Transform for a list of Events linked to Dates to a Visual Array

Posted on 2011-05-06
Last Modified: 2012-05-11
I am struggling to write a query that does not poke data into a temporary table and then return the temp table...

I have Activities (repeating) that occur during the day, and would like to send a summary email each day. At the moment we list the activities in the activity. It would be much better to present this as a matrix, but I can't get my head around the join. I am hoping someone has done this (there may even be a standard query for this).

ID      TimeStamp
2      2011-04-22 16:26:55.000
4      2011-04-22 17:10:00.000
4      2011-04-22 18:20:00.000
2      2011-04-22 20:40:00.000
4      2011-04-22 20:40:00.000
4      2011-04-22 23:10:00.000
4      2011-04-23 01:20:00.000
4      2011-04-23 03:20:00.000

Desired Result:

Time      1      2      3      4
16:00            X            
17:00                        X
20:00            X            X
23:00                        X
1:00                        X
3:00                        X
Question by:Bird757
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    What problems do you have with a temp table, because I think it is the easiest and fastest way to do this.
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    If you always have the same time frame you could just create a lot of subqueries something like

    select '16:00', (select count(*) from Activity where timestamp between '2011-04-22 16:00 and '2011-04-22 16:59' and ID = 1) as [1], select count(*)..... and ID=2) as [2]
    select '17:00', (select count(*) from Activity where timestamp between '2011-04-22 17:00 and '2011-04-22 17:59' and ID = 1) as [1], select count(*)..... and ID=2) as [2]

    Open in new window


    Author Comment

    Hi Nicobo

    I am a long-term fan of temp tables, and cursors. But these have started to cause me problems now the database size is in the Tb range....

    The emails I need to create are distributed to thousands of users so a Temp table could cause me issues. If there is no other way then that will be the solution; it is brute force, but simple.

    I was hoping for something a little more elegant....
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    There should be some ways to eleganize (is that a word?) this without the temp tables. You could create a while loop that builds a select query and then execute that as dynamic sql. Or you could create a static table with the number 1 to 24 and use that a the source instead of 24 unions.
    Did you try
    declare @x as table(...)
    instead of a temp table. Maybe those give you less problems.

    Accepted Solution


    With a bit of massaging I have got the PIVOT to work.... sample code out BooksOnLine below.

    But thanks for the idea of using a Table Array - I am using that to create my dynamic counter

    SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
    (SELECT PurchaseOrderID, EmployeeID, VendorID
    FROM Purchasing.PurchaseOrderHeader) p
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN
    ( [164], [198], [223], [231], [233] )
    ) AS pvt
    ORDER BY pvt.VendorID;
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    This looks quite different to me than you original question, but if your problem is solved I'm happy.

    Author Closing Comment

    Worked the solution out myself. But the input from EE did help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now