Sql-server – Generate wide table from lookup values

querysql server

I have a wide table that looks like this:

Table Windspeed
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  13.09   13.95   10.16                   11.71
09-Apr-19 00:05:00  12.17   11.98   12.54                   10.66

I need to generate a new table in the same format, but replace the column values from a lookup table that looks like this:

Table Powercurve
WindSpeedBin    Power_WdSpd Turb_Num
...
9.5             1216.9      1
10              1362.9      1
10.5            1476.3      1
11              1597        1
11.5            1701.5      1
12              1798.3      1
12.5            1828.5      1
13              1840        1
13.5            1846.1      1
...
9.5             1211.9      2
10              1342.9      2
10.5            1436.3      2
11              1517        2
11.5            1691.5      2
12              1713.3      2
12.5            1801.5      2
13              1833.1      2
13.5            1846.1      2
...

And finally, a third table that looks like this:

Table NameLookup
TName   Tag     TNum
A-004   A04     1
A-005a  A05     2
A-006a  A06     3
A-008a  A08     4
A-009   A09     5
...
E-009   E09     162

I need to generate a table that takes the values from table 1 (Windspeed), looks up the values in table 2 (Powercurve) (round to the nearest .5 to match WindSpeedBin), using the column names to lookup the Turb_Num value. Any ideas how to go about this? the end result would look something like this:

Table Expected_Production
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  1840    1846.1  1528.5                  1780.6
09-Apr-19 00:05:00  1828.5  1713.3  1843.9                  1614.5
...

This is for Microsoft SQL Server 2017

Best Answer

This should work for you. At least for the entries in the PowerCurve table that I copied over, it works correctly. This should get you close. See comments in the code for more details.

Assistance from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql on how to round to nearest .5/0.

/** FIRST - we setup the data and tables we need.

    Please note that I didn't copy the entire table of dbo.PowerCurve
    across, so my output is not as complete as yours, but this should
    get you close.
    */

DROP TABLE IF EXISTS dbo.Windspeed 
DROP TABLE IF EXISTS dbo.PowerCurve
DROP TABLE IF EXISTS dbo.NameLookup 

CREATE TABLE dbo.Windspeed 
    (
    [TimeStamp] DATETIME NOT NULL
    , [A-004] DECIMAL(5,2) NOT NULL
    , [A-005a] DECIMAL(5,2) NOT NULL
    , [A-006a] DECIMAL(5,2) NOT NULL
    , [E-009] DECIMAL(5,2) NOT NULL
    )

INSERT INTO dbo.Windspeed 
([TimeStamp], [A-004], [A-005a], [A-006a], [E-009])
VALUES ('4/9/2019 00:00:00', 13.09, 13.95, 10.16, 11.71)
    , ('4/9/2019 00:05:00', 12.17, 11.98, 12.54,10.66)

CREATE TABLE dbo.PowerCurve 
    (
    WindSpeedBIN DECIMAL(5,2) NOT NULL
    , Power_Wdspd DECIMAL(10,2) NOT NULL
    , Turb_Num INT NOT NULL
    )

INSERT INTO dbo.PowerCurve 
(WindSpeedBIN, Power_Wdspd, Turb_Num)
VALUES (13.0, 1840, 1)
    , (13.5, 1846.1, 1)
    , (13.0, 1833.1, 2)
    , (13.5, 1846.1, 2)
    , (12.0, 1798.3, 1)
    , (12.5, 1828.5, 1)
    , (12.0, 1713.3, 2)
    , (12.5, 1801.5, 2)

CREATE TABLE dbo.NameLookup 
    (
    TName VARCHAR(10) NOT NULL
    , Tag VARCHAR(10) NOT NULL
    , TNum INT NOT NULL
    )

INSERT INTO dbo.NameLookup
    (TName, Tag, Tnum)
VALUES ('A-004', 'A04', 1)
    , ('A-005a', 'A05', 2)
    , ('A-006a', 'A06', 3)
    , ('A-008a', 'A08', 4)
    , ('E-009', 'E09', 5)

Get the data

/** GET THE DATA
    - CTE_Source - unpivots so we can get the TNum and PowerCurve
    - CTE_Join - looks up the TNum in dbo.NameLocation and dbo.PowerCurve
    - CTE_Pivot - pivots the data back to make the output what we want
    **/

;WITH CTE_Source AS
    (
    SELECT [TimeStamp]
        , [TName]
        , [Value]
    FROM dbo.Windspeed AS W
        UNPIVOT ([Value] FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    )
, CTE_Join AS
    (
    --NOTE rounding answer from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql
    SELECT S.[TimeStamp]
        , S.[TName]
        , S.[Value]
        , CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2)  AS RoundedValue
        , NL.TNum
        , PC.Power_Wdspd
    FROM CTE_Source AS S
        LEFT OUTER JOIN dbo.NameLookup AS NL ON NL.TName = S.TName
        LEFT OUTER JOIN dbo.PowerCurve AS PC ON PC.WindSpeedBIN = CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2) AND PC.Turb_Num = NL.TNum 
    )
, CTE_Pivot AS
    (
    SELECT [TimeStamp]
        , MAX([A-004]) AS [A-004]
        , MAX([A-005a]) AS [A-005a]
        , MAX([A-006a]) AS [A-006a]
        , MAX([E-009]) AS [E-009]
    FROM CTE_Join AS J
        PIVOT (MAX(Power_Wdspd) FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    GROUP BY [TimeStamp]
    )
SELECT * FROM CTE_Pivot
Related Question