How to transform this IIF statment from Access to SQL

1

Hi All,
Can someone help me how to edit this Access SQL code to work in Microsoft SQL Server management:

SELECT Server.Outlook, Server.Region, Server.Country, Server.Territory, Server.Branch, Server.Tier, Server.Division, Server.Type, Server.Year, Server.sMonth, IIf([Data]="Extra Trips",[Qty],0) AS [Extra Trips], IIf([Data]="Hours",[Qty],0) AS Hours, IIf([Data]="Predict",[Qty],0) AS Predict, IIf([Data]="Reduc",[Qty],0) AS Reduc, IIf([Data]="Rev",[Qty],0) AS Rev, IIf([Data]="Service Req",[Qty],0) AS [Service Req], IIf([Data]="TCUP",[Qty],0) AS TCUP, IIf([Data]="Total Kills",[Qty],0) AS [Total Kills], IIf([Data]="Work Ord",[Qty],0) AS [Work Ord], IIf([Data]="FTE",[Qty],0) AS FTE
INTO Server_FINAL
FROM Server;

Thank you in advance!

2

Use a CASE WHEN condition, like this:

SELECT Server.Outlook, Server.Region, Server.Country, Server.Territory, Server.Branch, Server.Tier, Server.Division, Server.Type, Server.Year, Server.sMonth,
CASE WHEN [Data]='Extra Trips' THEN Qty ELSE 0 END AS [Extra Trips],
CASE WHEN [Data]='Hours' THEN Qty ELSE 0 END AS Hours,
CASE WHEN [Data]='Predict' THEN Qty ELSE 0 END AS Predict,
...
INTO Server_FINAL
FROM Server;

3

Thanks for the help. I forgot to mention that this Query from Access is "Make Table Query". Now, I have done as you said but have the problem. Get this error: "Incorrect syntax near the keyword 'FROM'." Just to say that I have selected "make Table" in SQL Design Query and write this code:

`SELECT Server.Outlook,
Server.Region,
Server.Country,
Server.Territory,
Server.Branch,
Server.Tier,
Server.Division,
Server.WOType,
Server.Year,
Server.Month,

		               CASE   WHEN [Data] = 'Extra Trips' THEN Qty ELSE 0 END AS [Extra Trips],
                                      case   when [Data] = 'Hours' then Qty else 0 end as Hours,
				  case   when [Data] = 'Predic' then Qty else 0 end as Predict,
				  case	 when [Data] = 'Reduc' then Qty else 0 end as Reduc,
				  case	 when [Data] = 'Rev' then Qty else 0 end as Rev,
				  case	 when [Data] = 'Service Req' then Qty else 0 end as [Service Req],
				  case	 when [Data] = 'TCUP' then Qty else 0 end as TCUP,
				  case	 when [Data] = 'Total Kills' then Qty else 0 end as [Total Kills],
				  case	 when [Data] = 'Work Ord' then Qty else 0 end as [Work Ord],
				  case	 when [Data] = 'FTE' then Qty else 0 end as FTE,

INTO Server_FINAL
FROM Server;

4

Remove the ending comma in above line

5

Its probably the (excess) comma on the end of your final CASE statement in the SELECT clause

You've got a

`

in front of the SELECT too, but I assume that was a Cut & Paste typo, otherwise you would have got a different error message