SQL Using Joins and combining 2 rows from one join to one row with values in different columns

user_14206569 0
Viewed 39

I am combining some forecast tables with joins and cases and i have the following problem.

My end result needs to be one row per measure.

I gather the forecasts from REGISTER table where they are on separate rows, like Nightforecast row 1, Dayforecast row 2.

Below is my example SQL and further info.

The example SQL is a bit shortened, the whole SQL has 4 inner joins and 5 different tables to join together, but the problem seems to be only from the REGISTER table.

There was a lot of topics which were a bit similar to this one, but i did not find a suitable solution for my problem. Using Group By for example was not working on this case.

What am I missing here?

SELECT 
    MEASURE.KP as 'CSCS', Contract.CustomerNumber as 'CN',
    CASE 
       WHEN REGISTER.ID = '21' THEN REGISTER.FORECAST
       WHEN REGISTER.ID = '1' THEN REGISTER.FORECAST
       ELSE '0' 
    END AS 'ECsCt_DayForec',
    CASE 
       WHEN REGISTER.ID = '22' THEN REGISTER.FORECAST
       ELSE '0' 
    END AS 'ECsCt_NightForec'
FROM 
    MEASURE
INNER JOIN 
    REGISTER ON MEASURE.DEVICEID = REGISTER.DEVICEID

REGISTER table

DeviceId  ID  Forecast
----------------------
1         21  220000
1         22  300000

Current query result:

CSCS  CN  ECsCt_DayForec  ECsCt_NightForec
-------------------------------------------
4     3    0              220000
4     3    300000         0

Result I need

CSCS  CN  ECsCt_DayForec  ECsCt_NightForec
-------------------------------------------
4     3    300000         220000
sql-server sql
1 Answers

Your query has no Contract table. I assume that is just an error in simplifying the query.

To solve your problem, use aggregation:

SELECT m.KP as CSCS, c.CustomerNumber as CN,
       MAX(case when r.ID = '21' then r.FORECAST
                when r.ID = '1' then r.FORECAST
                else '0'
            end) as ECsCt_DayForec,
       MAX(case when r.ID = '22' then r.FORECAST
                else '0'
           end) as ECsCt_NightForec
FROM MEASURE m JOIN
     REGISTER r
     ON m.DEVICEID = r.DEVICEID
GROUP BY m.KP, c.CustomerNumber ;

Notes:

  • Use single quotes only for strings and dates. Do not use them for column names. Your column names don't need to be escaped, so I just removed them.
  • Table aliases make the query easier to write and to read.
  • I don't know the data type of forecast. I would guess that it is a number of some sort. If that is true, use 0 without single quotes. Don't mix string and number types.
user_11440350
Related