CODEWITHSUNDEEP
sql-servergroup-bypivot-table
Tom Klarin
Tom Klarin

Reputation: 33

Using PIVOT and Group By to Aggregate histograms?

I have a table in SQL server that contains a lot of 2d histograms (each histogram contains 32 index and values) as follows:

enter image description here

These histograms are logged in a time series which means in each specific date we have a histogram with a unique ID. I want to get the average of these histograms --weekly--. It means for each week of a month (if there is a data in that week), I want to have one histogram for each unique ID. Then I want to convert these two 2d histograms into 1d. At the end my desired table should have one row for each unique ID that contains the value of histogram. As an example, I would like to have the below table at the end:

   ID             Date (year), Month , week , index1 , index2,  ... , Index31 , Index32
   273854048        2019     ,   4   ,  1   ,   23   ,  234  ,  ... ,    0    ,  95.00
   287092805        2019     ,   4   ,  4   ,   0.0  ,  95.0 ,  ... ,    43   ,   64
   ...             ...

It seems PIVOT and group by should be used, but it seems very complicated.

Upvotes: 0

Views: 181

Answers (1)

Charlieface
Charlieface

Reputation: 72040

A simple pivot clause should do the trick.

The columns will be ID, Date, 1, 2, 3....

SELECT pvt.*
FROM YourTable t
PIVOT (
    MIN(t.[Value]) FOR t.[Index] IN
      ([1]),([2]),([3]),([4]),([5]),([6]),([7]),([8]),([9]),([10]),([11]),([12]),([13]),([14]),([15]),([16]),([17]),([18]),([19]),([20]),([21]),([22]),([23]),([24]),([25]),([26]),([27]),([28]),([29]),([30]),([31]),([32])
) pvt

You can add a relevant ORDER BY if necessary

Upvotes: 2

Related Questions