SQL SERVER – How to Use Decode in SQL Server?

One of the readers of the blog has sent me a question regarding how to use the DECODE function in SQL Server.

SELECT DECODE(Letters, 'First',1, 'Second',2, 'Third',3, 0) AS LN
 FROM LettersTable

In SQL Server the equivalent code is CASE statement. Here are the examples regarding how DECODE can be written in SQL Server.

SELECT CASE WHEN Letters = 'First' THEN 1
WHEN Letters = 'Second' THEN 2
WHEN Letters = 'Third' THEN 3
ELSE 0 END AS LN
FROM LettersTable

Here are few alternative blog posts.

SQL SERVER – Alternate to AGENT_DATETIME Function

Sometimes back I posted about interesting function AGENT_DATETIME functions which will convert integers into datetime values. Someone commented that there might be some permission issues using this function which is only available in msdb database. So I write this post to give you alternate method without using any such functions.

SQL SERVER – What is T-SQL Window Function Framing? – Notes from the Field #102

One of the best kept secrets of T-SQL window functions, also called windowing or windowed functions, is framing. Framing was introduced with SQL Server 2012, and it allows the set of rows, or window, to be defined very granularly. For example, when calculating a running total, each row needs to see an increasing number of values to perform the calculation. Row 1 needs to see just row 1. Row 2 needs to see rows 1 and 2. Row 3 needs to see rows 1, 2, and 3. Framing allows each row to have a distinct set of rows for the window function calculation.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Potential Bottlenecks for Performance
Next Post
SQL SERVER – How to ALTER CONSTRAINT

Related Posts

16 Comments. Leave new

  • Nick McDermaid
    April 24, 2011 5:49 am

    A more concise version, following the format of DECODE a little more:

    SELECT CASE Letters
    WHEN ‘First’ THEN 1
    WHEN ‘Second’ THEN 2
    WHEN ‘Third’ THEN 3
    ELSE 0 END AS LN
    FROM LettersTable

    Something else to note is that DECODE is an overloaded function that can take a variable number of parameters. I’m not aware of any functions like this in SQL Server.

    Reply
  • Would be great to see a list of all the Sql functions etc that allow variable usage..

    Reply
  • OK, dumb question… you’re referring to DECODE from Oracle, right? Might be helpful to add that reference for your non-oracle fluent readers. I was thinking DECODE was a new feature of Denali or something. Thanks Pinal.

    Reply
  • CREATE procedure LiabilitySchedules(@EndDate datetime,@Schedule varchar(100))
    as begin

    declare @PrevEndDate datetime

    set @EndDate=@EndDate
    set @PrevEndDate=dateadd(year,-1,@EndDate)

    select distinct T0.[U_EnvSubHd],
    (select U_EnvMisHd from [@ENV_SUB_HEAD] where Code=T0.U_EnvSubHd)[Sub Head],
    (select isnull(sum(T5.[credit]-T5.[Debit]),0.00) FROM OACT T4
    INNER JOIN JDT1 T5 ON T4.AcctCode = T5.Account INNER JOIN OJDT T6 ON T5.TransId = T6.TransId

    and T6.RefDate<=@EndDate and T4.U_EnvSubHd=T0.U_EnvSubHd and T4.U_EnvMainHd=T0.U_EnvMainHd )[Present],

    (select isnull(sum(T5.[credit]-T5.[Debit]),0.00) FROM OACT T4
    INNER JOIN JDT1 T5 ON T4.AcctCode = T5.Account INNER JOIN OJDT T6 ON T5.TransId = T6.TransId and
    T6.RefDate<=@PrevEndDate and T4.U_EnvSubHd=T0.U_EnvSubHd and T4.U_EnvMainHd=T0.U_EnvMainHd)[Previous]
    from [dbo].OACT T0 with(nolock)
    where T0.[U_EnvMainHd]=@Schedule and T0.[U_envAlPha]='Liability'
    end

    Dear Pinal Dave,

    This is my query.
    in this present and previous are the 2 columns which are written in sub query.
    and this query query is taking more than 15 min to execute as the data is very huge.
    please guide in a way to accelerate performance of this query.
    and is there any option instead of sub queries.

    Reply
  • Roman Hansen
    August 4, 2011 1:25 am

    Vivek, You want Pinal doing your job ! Good Luck with that…
    By the way:
    1) Analyze the execution plan
    2) Database Engine Tuning Advisor will give you some advice about indexes.
    3) DMVs

    Reply
  • i want to use sum() function with “case” in sqlserver, if i m using this, it is asking for the field in “when” clause to be used in “group by” clause – this way, i m not getting correct result
    can i use “case” in “group by “clause

    Reply
    • Paul Berndsen
      August 30, 2012 3:00 pm

      Yes you can.
      select [total value] = sum(value)
      , [amount] = case
      when amount = 1 then
      ‘one’
      when amount = 2 then
      ‘two’
      else
      ‘more’
      end
      from orders
      group by case
      when amount = 1 then
      ‘one’
      when amount = 2 then
      ‘two’
      else
      ‘more’
      end

      Reply
  • hemanth kumar
    October 1, 2012 1:06 pm

    case what is case exactly when i execute my query it says “Incorrect syntax near the keyword ‘CASE’.”

    Reply
  • If Possible add this case
    when amount = 1 OR amount = 1 then
    ‘one’
    then
    ‘two’
    else
    ‘more’
    end

    Reply
  • Glauco Guerrero
    May 9, 2013 2:40 am

    In fact there is no DECODE in SQL Server as far as I know, but I have achieved the same result in T-SQL but you have to know up front the columns you want to receive, here’s an example.

    Consider you have table of employees with 3 departments : 1=FINANCE, 2=IT and 3=ACCOUNTING,

    SELECT SUM(IT) AS IT,
    SUM(IT) AS IT,
    SUM(FINANCE) AS FINANCE,
    SUM(ACCOUNTING) AS ACCOUNTING,
    FROM (
    SELECT
    CASE WHEN DEPT=1 THEN 1 ELSE 0 END AS FINANCE,
    CASE WHEN DEPT=2 THEN 1 ELSE 0 END AS IT,
    CASE WHEN DEPT=3 THEN 1 ELSE 0 END AS ACCOUNTING,
    FROM EMPLOYEES )
    ;

    Of course this will bring a DECODE for all ROWS, in case you want a group or filter the rows the SQL would be different.

    Reply
  • ABHIJITH K S
    April 14, 2014 2:11 pm

    sir,
    What is the use of STUFF and XML PATH in SQL SERVER..
    Please Explain With Examples

    Reply
  • Hi sirji,

    I need a solution for the below query.

    I have a table temp with column a,b,c,d

    A B C D
    1 1 1 m
    1 2 1 d
    1 3 1 w
    2 1 1 m
    2 2 1 d
    2 2 1 w

    SELECT CASE WHEN B=1 AND C=1 THEN D END as T1,
    CASE WHEN B=2 AND C=1 THEN D END as T2,
    CASE WHEN B=3 AND C=1 THEN D END as T3
    FROM TEMP
    WHERE A=1

    MY RESULT SET IS
    T1 T2 T3
    m NULL NULL
    NULL d NULL
    NULL NULL w

    NOW I NEED A RESULT SET WITH SINGLE ROW
    T1 T2 T3
    m d w

    Reply
    • select
      MAX( T1) T1,MAX(T2) T2,MAX(T3)T3 FROM
      (SELECT CASE WHEN B=1 AND C=1 THEN D END as T1,
      CASE WHEN B=2 AND C=1 THEN D END as T2,
      CASE WHEN B=3 AND C=1 THEN D END as T3
      FROM #TEMP
      WHERE A=1)TE

      Reply
  • Hi .. can anyone help in modiying the below decode statement to run using CASE in sql server

    Select f.fge_code_id, f.fge_libelle as incident,
    decode(f.fge_code_id,’236′,’920′,’243′,’720′,’218′,’910′,’220′,’910′,’247′,’720′,’251′,’799′,’215′,’610′,’185′,’899′,’255′,’799′,’221′,’930′,’266′,’440′,’183′,
    ‘410’,’265′,’410′,’217′,’910′,198,499,187,440,’183′,’410′,
    186,460,637,899,263,910,184,499,261,499,’257′,’899′,’194′,’499′,190, 460,633,710,’241′,’1199′,’213′,’610′,’232′,’1130′,203,499,632,110,924,899,212,610,250,730,
    189,499,267,450,235,920,242,999,1430,1320,192,610,200,499,’1′) as plugsellcode
    From contrats.fgs f
    order by fge_code_id

    Reply
  • Tulio Galdámez
    December 8, 2020 3:54 am

    Excelente, muchas gracias por el tip.

    Reply

Leave a Reply

Menu
Exit mobile version