SQL Query Transpose not returning negative numbers

Question

I have a table that I am transposing by using a database view (using SQL Server). This table has positive and negative values. All of the negative values are returned as zero. Any ideas on how I can correct this?

Example of my original table:

Year	Peak	Value_A	Value_B
2016	AM	15.156546	51.265146
2018	AM	-15.5998	-14.1565
2028	AM	16.3216	18.5611
2016	IP	-0.01656	-0.0026554
2018	IP	-0.00159	-0.59874
2028	IP	1.98438	3.362498
2016	PM	-5.65436	8.6951
2018	PM	2.2316	3.859117
2028	PM	-3.99842	-9.620148

The result of my transposed view:

Peak	Value_A_2016	Value_A_2018	Value_A_2028	Value_B_2016	Value_B_2018	Value_B_2028
AM	15.156546	0	16.3216	51.265146	0	18.5611
IP	0	0	1.98438	0	0	3.362498
PM	0	2.2316	0	8.6951	3.859117	0

This is the SQL script for the view:

SELECT Peak, MAX(CASE WHEN T .YEAR = 2016 THEN T .[Value_A] ELSE 0.00 END) AS Value_A_2016, 
       MAX(CASE WHEN T .YEAR = 2018 THEN T .[Value_A] ELSE 0.00 END) AS Value_A_2018, 
       MAX(CASE WHEN T .YEAR = 2028 THEN T .[Value_A] ELSE 0.00 END) AS Value_A_2028, 
       MAX(CASE WHEN T .YEAR = 2016 THEN T .[Value_B] ELSE 0.00 END) AS Value_B_2016, 
       MAX(CASE WHEN T .YEAR = 2018 THEN T .[Value_B] ELSE 0.00 END) AS Value_B_2018, 
       MAX(CASE WHEN T .YEAR = 2028 THEN T .[Value_B] ELSE 0.00 END) AS Value_B_2028
FROM gisadmin.Table_1 AS T
GROUP BY Peak

Thanks very much for any assistance with this.

GMB · Accepted Answer · 2023-03-13 22:38:29Z

2

MAX(CASE 
    WHEN T.YEAR = 2018 THEN T.[Value_A] 
    ELSE 0.00
END) AS Value_A_2018

The problem is with the else branch, which returns 0 on rows that do not match the year predicate. Obviously 0 is greater than any negative value, so this is what MAX returns, hence "masking" the actual value.

Instead, you can just remove the else branch ; unmatched rows yield null values, that max ignores.

You can still assign a default value of 0 to null columns afterwards with coalesce(), if that’s what you want:

COALESCE(
    MAX(CASE 
        WHEN T.YEAR = 2018 THEN T.[Value_A] 
    END),
    0
) AS Value_A_2018

answered Mar 13, 2023 at 22:38

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2023-03-13 22:41:58Z

Your problem is that 0.00 is bigger that -0.003

if you remove the else clause, so that there is a NULL returned, you can get all the umbers

SELECT Peak, MAX(CASE WHEN T .YEAR = 2016 THEN T .[Value_A] END) AS Value_A_2016, 
       MAX(CASE WHEN T .YEAR = 2018 THEN T .[Value_A] END) AS Value_A_2018, 
       MAX(CASE WHEN T .YEAR = 2028 THEN T .[Value_A] END) AS Value_A_2028, 
       MAX(CASE WHEN T .YEAR = 2016 THEN T .[Value_B] END) AS Value_B_2016, 
       MAX(CASE WHEN T .YEAR = 2018 THEN T .[Value_B] END) AS Value_B_2018, 
       MAX(CASE WHEN T .YEAR = 2028 THEN T .[Value_B] END) AS Value_B_2028
FROM Table_1 AS T
GROUP BY Peak

Peak	Value_A_2016	Value_A_2018	Value_A_2028	Value_B_2016	Value_B_2018	Value_B_2028
AM	15.1565	-15.5998	16.3216	51.2651	-14.1565	18.5611
IP	-0.0166	-0.0016	1.9844	-0.0027	-0.5987	3.3625
PM	-5.6544	2.2316	-3.9984	8.6951	3.8591	-9.6201

fiddle

Collectives™ on Stack Overflow

SQL Query Transpose not returning negative numbers

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related