I have this schema
| Name | type | description |
|---|---|---|
| dt | Varchar(19) | event timestamp |
| type | Varchar(64) | Event type |
And this sample table
| dt | type |
|---|---|
| 2020-09-08 19:35:17 | other |
| 2020-12-14 12:07:44 | buy |
| 2021-07-08 14:35:25 | sell |
| 2021-10-15 17:07:30 | buy |
| 2021-12-08 06:35:18 | other |
| 2022-12-14 12:07:44 | buy |
| 2022-09-08 19:35:17 | other |
| 2022-12-14 12:07:44 | sell |
I have group the data by quarters in year 2021 and count the each type in each quarter. Query should produce the following output: (this may be inconsistent with the sample table, this is just for example)
| period | events |
|---|---|
| Q1'21 | other=1; sell =1; buy =3 |
| Q2'21 | buy = 1 |
| Q3'21 | sell = 2; other = 4 |
Q1'21 means quarter 1 2021 and events column counts the types in each quarter.
I am thinking to use CASE WHEN for period column, but have no idea how to write query for events column.
How can I write query for events column?
Seems like what you need to do here is first COUNT the number of instances per quarter, year and type and then string aggregate afterwards.
This assumes you are on a supported version of SQL Server. If you can't use STRING_AGG due to using a much older version of SQL Server, you'll need to use the "old" FOR XML PATH (and STUFF) method for your string aggregation.
WITH CTE AS(
SELECT DATEPART(YEAR, dt) AS Year,
DATEPART(QUARTER,dt) AS Quarter,
type,
COUNT(*) AS COUNT
FROM dbo.YourTable
GROUP BY DATEPART(YEAR, dt),
DATEPART(QUARTER,dt),
type)
SELECT CONCAT('Q',Quarter,'''',RIGHT(Year,2)) AS Period,
STRING_AGG(CONCAT(type,'=',Count),';') AS Events
FROM CTE
GROUP BY Quarter,
Year;