T-SQL IF ELSE Variable declaration

Question

Im trying to construct a recordset with IF ELSE statements, i have read a lot on MSDN, but i can't seem to get this working. The above code is not working, mostly because the syntax is wrong, i have included it here to give a general idea of what im trying to do.

SELECT FagNavn, Ugedag, frakl, tilkl, 

IF Ugedag = 'Mandag'
BEGIN
    (frakl + ' - ' + tilkl) AS startSlutMandag
END
ELSE
startSlutMandag = ''
IF Ugedag = 'Tirsdag'
BEGIN
    (frakl + ' - ' + tilkl) AS startSlutTirsdag
END
ELSE
    startSlutTirsdag = ''
IF Ugedag = 'Onsdag'
BEGIN
    (frakl + ' - ' + tilkl) AS startSlutOnsdag
END
ELSE
    startSlutOnsdag = ''
IF Ugedag = 'Torsdag'
BEGIN
    (frakl + ' - ' + tilkl) AS startSlutTorsdag
END
ELSE
    startSlutTorsdag = ''
IF Ugedag = 'Fredag'
BEGIN
    (frakl + ' - ' + tilkl) AS startSlutFredag
END
ELSE
    startSlutFredag = ''

FROM [VisWebHoldSkema]

Here is a sample of the records in the database:

FagNavn                      Ugedag    frakl    tilkl
-----------------------------------------------------
Engelsk, G                   Torsdag   13:00    13:50
Dansk som andetsprog, G      Mandag    15:25    16:15
Religion                     Mandag    17:00    17:50
Engelsk, E                   Torsdag   12:20    13:05
Religion                     Tirsdag   10:10    11:00
Religion                     Tirsdag   11:25    12:15
Dansk                        Mandag    09:10    10:00
Matematik                    Torsdag   09:10    10:00
Matematik                    Fredag    12:25    13:15

What i would like is a recordset like this:

FagNavn                      startSlutMandag    startSlutTirsdag    startSlutOnsdag    startSlutTorsdag    startSlutFredag    
--------------------------------------------------------------------------------------------------------------------------
Engelsk, G                   13:00 - 13:50
Dansk som andetsprog, G                          15:25 - 16:15
Religion                     17:00 - 17:50
Engelsk, E                                                           12:20 - 13:05
Religion                                                                                                    10:10 - 11:00
Religion                                         11:25 - 12:15
Dansk                                                                09:10 - 10:00
Matematik                                                                                                   09:10 - 10:00
Matematik                    12:25    13:15

Please, any help is greately appriciated.

UPDATE

SELECT FagNavn, Ugedag, frakl, tilkl, 
Case When Ugedag = 'Mandag' 
     Then frakl + ' - ' + tilkl
     Else '' End AS startSlutMandag,
Case When Ugedag = 'Tirsdag' 
     Then frakl + ' - ' + tilkl
     Else '' End AS startSlutTirsdag
Case When Ugedag = 'Onsdag' 
     Then frakl + ' - ' + tilkl
     Else '' End AS startSlutOnsdag
Case When Ugedag = 'Torsdag' 
     Then frakl + ' - ' + tilkl
     Else '' End AS startSlutTorsdag
Case When Ugedag = 'Fredag' 
     Then frakl + ' - ' + tilkl
     Else '' End AS startSlutFredag
FROM [VisWebHoldSkema] 

Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'Case'.

Your update misses the commas at the end of the case statements — Justin Wignall
– Justin Wignall, Commented Jun 6, 2013 at 13:04

Charles Bretana · Accepted Answer · 2013-06-06 12:52:31Z

4

You can't use If...Else inside of a single SQL Select statement. Use Case for that, as in

SELECT FagNavn, Ugedag, frakl, tilkl, 
    Case When Ugedag = 'Mandag'
         Then frakl + ' - ' + tilkl
         Else '' End AS startSlutMandag,
    Case When Ugedag = 'Tirsdag'
         Then frakl + ' - ' + tilkl
         Else '' End  AS startSlutTirsdag
    etc. ...
 FROM [VisWebHoldSkema]

answered Jun 6, 2013 at 12:52

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

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

3 Comments

user1359448 Over a year ago

Thanks a lot, but if i add a third Case statement i get an error, i have updated my post.

user1359448 Over a year ago

Yeah, was missing the commas. Thanks a lot.

JC Ford Over a year ago

Also note if you're using case to examine a single variable you can shorten the expression: Case Ugedag When 'Mandag' Then '...' When 'Tirsdag' Then '...' Else '...' End

Alexander · Accepted Answer · 2013-06-06 12:53:25Z

0

You use the CASE statement instead of IF in a SELECT statement: CASE (Transact-SQL)

answered Jun 6, 2013 at 12:53

Alexander

2,4771 gold badge16 silver badges17 bronze badges

Collectives™ on Stack Overflow

T-SQL IF ELSE Variable declaration

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related