SQL Pivot Rows to Columns

Databases
1

Hello everyone, I need your help.

I have to make this output using Microsoft SQL Server 2019

Senza titolo

I have done several researches on the web and various attempts but I can’t do more than this using a pivot

Senza titolo 1
Senza titolo 1183×147 1.36 KB

Here demo

Thanks in advance for any help.

2

a SQL query cannot output multiple level results. You’ll need some sort of intermediate processing to generate a multi-level result.

You can generate the row by JOIN’ing the same table to itself, but you’d have to join it for each account manually;

SELECT p.DtHour,p.Total,p.RG,p.SC,p.RP,c.Total,c.RG,c.SC,c.RP FROM do_table p 
FULL JOIN do_table c
ON p.DtHour = c.DtHour
WHERE c.Account = N'CTNA'
AND p.Account = N'PLRM'
1 Like
3

Thank you, I understand… but to get the Total of the individual columns Total, RG, SC, RP How can I do it?

4

‘total’ makes no contextual sense if your rows are indexed by a timestamp?

1 Like
5

I regretfully had to abandon the initial project because of what you suggested***

I mean this

Senza titolo
Senza titolo652×109 6.89 KB

Here demo

***a SQL query cannot output multiple level results. You’ll need some sort of intermediate processing to generate a multi-level result.
You can generate the row by JOIN’ing the same table to itself, but you’d have to join it for each account manually;

6

I assume you abandoned because you cannot specify ahead of time how many accounts there will be.

SELECT DtHour,Account,SUM(Total) Total,SUM(RG) RG,SUM(SC) SC,SUM(RP) RP
FROM do_table p 
GROUP BY DtHour,Account
WITH ROLLUP
1 Like
7

Thank you, but why a double line reporting the total?

Senza titolo
Senza titolo431×167 7.16 KB

Here demo

8

So the Rollup is doing Subtotals for each grouping category.

We told it to group on two levels - Time (hour) and Account. The order is important.

So row 1 is “The (sum of the) 9 AM value(s) for Account CTNA”
row 2 is The (sum of the) 9 AM value(s) for Account PLRM"
We’ve come to the end of our groups, so the system now subtotals the first level of grouping (Account).
Row 3 is The sum of the 9AM values for all accounts (identifyable because Account is null).
We’ve come to the end of the top level of groupings, so the system rolls up to the next level of grouping (Hour)…
Row 4 is the sum of all hour subtotals (Identifiable because both Account and Hour are null).

To see how this works a bit better, try adding another set of data to your table, with a different timestamp.

1 Like