Max and Decode convert in SQL Server

Question

I want to convert this piece of the query to run in SQL Server its taken from an Oracle query:

SELECT    
    x.JudgmentMonth,
    MAX(DECODE(x.RecordType, '1', x.Volumes)) CONSUMER,
    MAX(DECODE(x.RecordType, '2', x.Volumes)) COMMERCIAL
FROM    
    (SELECT    
         r.JudgmentMonth, r.RecordType, r.Volumes
     FROM    
         dset r) x
GROUP BY 
    x.JudgmentMonth

I am not familiar with SQL Server any help will much appreciated.

I don't know Oracle. What does the DECODE function do? – Sean Lange Jun 08 '18 at 15:53 — Sean Lange, Jun 08 '18 at 15:53

score 3 · Answer 1 · answered Jun 08 '18 at 15:54

3

Oracle's decode is esentially a shorthand for the ANSI-SQL case expression:

MAX(CASE x.RecordType WHEN '1' THEN x.Volumes END) CONSUMER,
MAX(CASE x.RecordType WHEN '2' THEN x.Volumes END) COMMERCIAL

answered Jun 08 '18 at 15:54

Mureinik

297,002
52
306
350

I have RecordType colum which returns values 1,2 and 3 I managed to replace 3 to 2 i need to replace 1 to read as consumer and 2 as commercial, i guess CASE would replace 1 and 2 to Consumer and Commercial as string? – Vinnie S Jun 08 '18 at 15:57

Max and Decode convert in SQL Server

1 Answers1