[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-06
7
Medium Priority
?
249 Views
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).

Example:
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
18:00                        
19:00                        
20:00            X            X
21:00                        
22:00                        
23:00                        X
0:00                        
1:00                        X
2:00                        
3:00                        X
4:00                        
0
Comment
Question by:Bird757
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

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

Expert Comment

by:Nico Bontenbal
ID: 35705541
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]
union
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



0
 

Author Comment

by:Bird757
ID: 35705628
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....
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35705679
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.
0
 

Accepted Solution

by:
Bird757 earned 0 total points
ID: 35706710
Hi,

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
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY pvt.VendorID;
 
0
 
LVL 22

Expert Comment

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

Author Closing Comment

by:Bird757
ID: 35744607
Worked the solution out myself. But the input from EE did help.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard 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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

868 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