Pivot query

  • 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 3 weeks, 4 days ago by  Roust_m.
  • 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.

  • 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.

  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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