Pivot query

Question

Post reply

Pivot query

Roust_m

SSCoach

Points: 17410
More actions
April 23, 2024 at 12:17 pm

#4414497
Hi,
I have the following table:
```
CREATE TABLE [dbo].[UnitProdFinal](
[YearMonth] [int] NULL,
[SerialNumber] [varchar](50) NULL,
[EnergyProdkWh] [decimal](18, 0) NULL
) ON [PRIMARY]
```
It contains solar production for my 41 solar panels and a total for each month from Jan 2017 to March 2024 (87 months in total). The data looks like this:
YearMonth SerialNumber EnergyProkWh
201701 p1 23.5
201701 p2 25.5
....
201701 p41 22.5
201701 Total: 945.5
201702 p1 26.7
etc.
I need to create a query so it pivots the data in such a way that panel serial numbers become the column names:
YearDate p1 p2 ... p41 Total
201701 23.5 31.1 ... 31.2 945.5
201702 24.3 29.3 ... 21.4 834.6
...
202303 26.4 27.1 ... 25.6 790.2
I also need to present it so the first column contains panels serial numbers and the YearMonth becomes column names:
SerialNumber 201701 201702 ........ 202403
p1
p2
...
p41
Total
Thanks.
- This topic was modified 7 months, 1 week ago by Roust_m.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16157 More actions · Answer 1

search these forums for PIVOT or CROSSTAB queries and you will find a few answers - google also has plenty of examples.

then get back to us with issues if any after trying the 2 options I mentioned above.

Roust_m SSCoach Points: 17410 More actions · Answer 2

I did search for the answer and found few examples here:

https://www.mssqltips.com/sqlservertip/7233/sql-pivot-sql-unpivot-examples-transform-data/

e.g.:

SELECT 'SalesYTD' AS SalesYTD, [Europe], [North America], [Pacific]

FROM

(

SELECT SalesYTD, [Group]

FROM [Sales].[SalesTerritory]

) AS TableToPivot

PIVOT

(

SUM(SalesYTD)

FOR [Group] IN ([Europe], [North America], [Pacific])

) AS PivotTable;

My main problem is that both the number of months and number of panels are too big to name them in the query like above:

FOR [Group] IN ([Europe], [North America], [Pacific])

I need something which would name the columns automatically somehow.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Here's an example. Get cutting and pasting and the rest of the columns are done.

DROP TABLE IF EXISTS #UnitProdFinal;

CREATE TABLE #UnitProdFinal
(
    YearMonth INT NULL
   ,SerialNumber VARCHAR(50) NULL
   ,EnergyProdkWh DECIMAL(18, 4) NULL
);

INSERT #UnitProdFinal
(
    YearMonth
   ,SerialNumber
   ,EnergyProdkWh
)
VALUES
(201701, 'P1', 23.5)
,(201701, 'P2', 23.5)
,(201701, 'P41', 22.5)
,(201702, 'P1', 22.5)
,(201702, 'P3', 222.5)
,(201702, 'P4', 3.5);

SELECT *
FROM #UnitProdFinal upf;

SELECT upf.YearMonth
      ,p1 = SUM (   CASE
                        WHEN upf.SerialNumber = 'P1' THEN
                            upf.EnergyProdkWh
                    END
                )
      ,p2 = SUM (   CASE
                        WHEN upf.SerialNumber = 'P2' THEN
                            upf.EnergyProdkWh
                    END
                )
      ,p3 = SUM (   CASE
                        WHEN upf.SerialNumber = 'P3' THEN
                            upf.EnergyProdkWh
                    END
                )
      ,p4 = SUM (   CASE
                        WHEN upf.SerialNumber = 'P4' THEN
                            upf.EnergyProdkWh
                    END
                )
      ,p41 = SUM (   CASE
                         WHEN upf.SerialNumber = 'P41' THEN
                             upf.EnergyProdkWh
                     END
                 )
      ,Total = SUM (upf.EnergyProdkWh)
FROM #UnitProdFinal upf
GROUP BY upf.YearMonth;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin