'Counting variable by day and time with day and time in column header
I am looking to write a query that gives a count of purchases grouped by day and hour (from variable of date and time of purchase).
However, column headers should contain the date and hour as such:
| ID | Tuesday, 11-12 | Tuesday, 12-13 |
|---|---|---|
| Xxxxx | 4 | 6 |
| Xxxxx | 1 | 8 |
Variables include ID, Date of purchase (DD-MM-YY timestamp), QTY
Having done some reading, I am not entirely convinced this is possible? But am unsure and could be misinformed.
Thanks for your help in advance. Any suggestions would be greatly appreciated.
If any more information is needed, please let me know.
Solution 1:[1]
If you're using SQL Server, you can solve this with the PIVOT clause. Derive the unique dates you want in the query, then use PIVOT to put across into columns. I'd provide an example but I don't have the time this moment.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | datarocker |