Complex Pivot using the existing data

pepcoder 1 Reputation point
2024-04-07T09:23:56.2033333+00:00

--ALTER PROCEDURE Energy.GetGTSTDataForManualEntry

--(

-- @Date DateTime

-- )

--AS

BEGIN

DECLARE @Date DateTime = '2024-03-29 00:00:00.000';

DECLARE @CalendarID INT = Time.GetCalendarIDByLocationCode('Energy');

DECLARE @DayPeriod Time.SqlPeriod = Time.CreatePeriodFromPlantStartDate(@CalendarID, @Date - 1, 'Day');

DECLARE @StartDate DATETIME;

DECLARE @EndDate DATETIME;

SET @StartDate = @DayPeriod.GregorianStartDate;

SET @EndDate = @DayPeriod.GregorianEndDate;

DECLARE @TABLE TABLE (

ID INT,  

StartDate DateTime,  

EndDate DateTime,  

Name NVarchar(250),  

POINTNUMBER INT,  

StartDateValue DECIMAL(38,6),  

StartDateCorected DECIMAL(38,6),  

EndDateValue DECIMAL(38,6),  

EndDateCorrected DECIMAL(38,6),  

DayValue DECIMAL(38,6),  

NetEnergy DECIMAL(38,6)  

);

WITH RankedTable AS (

SELECT   

    ROW_NUMBER() OVER (ORDER BY ES.Groups) AS RowNum,  

    --CASE WHEN ES.Groups LIKE 'GT%' THEN LEFT(ES.Groups,4)  



    -- ELSE LEFT(ES.Groups,5) END AS Name,  

	ES.Groups as Name,

    ES.POINTNUMBER,  

    MAX(CASE WHEN GSTTIME = @StartDate THEN VALUE END) AS StartDateValue,  

    MAX(CASE WHEN GSTTIME = @EndDate THEN VALUE END) AS EndDateValue,  

    MAX(CASE WHEN GSTTIME = @EndDate THEN VALUE END) - MAX(CASE WHEN GSTTIME = @StartDate THEN VALUE END) AS DayValue  

FROM   

    Energy.ScadaStations ES  

LEFT JOIN  

    Energy.HOUR_AVG_1 EH ON EH.POINTNUMBER = ES.POINTNUMBER  

WHERE   

    ES.POINTNUMBER IN (  

        79508,     

        79510,      

        79513,      

        79515,      

        79517,      

        79519,      

        79521,      

        79523,      

        79525,      

        79527,      

        79529,      

        79531,      

        79533,      

        79535,     

        79509,      

        79511,      

        79514,      

        79516,      

        79518,      

        79520,      

        79522,      

        79524,      

        79526,      

        79528,      

        79530,      

        79532,      

        79534,      

        79536      

    )  

GROUP BY   

    ES.Groups,   

    ES.POINTNUMBER  

)

INSERT INTO @TABLE (ID, StartDate, EndDate, Name, POINTNUMBER, StartDateValue, EndDateValue, DayValue)

SELECT

RowNum AS ID,  

@StartDate AS StartDate,  

@EndDate AS EndDate,  

Name as Station,  

POINTNUMBER,  

StartDateValue,  

EndDateValue,  

DayValue  

FROM

RankedTable;  

SELECT

A.ID,   

LEFT(A.Name,4) as Station,   

A.POINTNUMBER,   

**A.StartDateValue,**   

**ISNULL(A.StartDateCorected, A.StartDateValue) AS StartDateCorected,**  

**A.EndDateValue,**   

**ISNULL(A.EndDateCorrected, A.EndDateValue) AS EndDateCorrected,**  

**A.DayValue--,**  

FROM

 @TABLE A

 INNER JOIN @TABLE B ON A.ID =B.ID 

 

END

I would like to pivot the columns highlighted in bold on the basics of Station column . Please suggest a way forward

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,823 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
97 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions
{count} votes

1 answer

Sort by: Most helpful
  1. pepcoder 1 Reputation point
    2024-04-07T09:27:47.7966667+00:00

    Fixed the issue


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.