How to use CASE statements in SQL Query : + SQL SERVER 2005

First lets create a sample table named testable

CREATE TABLE [dbo]. [testable]
(
[MaterialID] [int] NOT NULL,
[type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[value] [decimal](18, 0) NULL
) ON [PRIMARY]

Now lets insert some values like

INSERT INTO testtable
SELECT 1,'cem','RAO',2
UNION ALL
SELECT 5,'wtr','Water',4
UNION ALL
SELECT 6,'cem','rao',5

Now write a query to that selects the column MaterialID, and two assigned columns named CementDescription and TestDescription where the CementDescription column should be filled with ‘CEMEMT’ for each row in the table where the type is ‘cem’ and the WaterDescription column should be filled with ‘Water’ for each row of the table where the type is ‘wtr’.

SELECT MaterialID,
CASE WHEN type = 'cem' THEN 'CEMENT' ELSE NULL END as CementDescription ,
CASE WHEN type = 'wtr' THEN 'WATER' ELSE NULL END as WaterDescription
FROM testtable

OUTPUT:

MaterialID CementDescription WaterDescription

1 CEMENT NULL
2 NULL WATER
3 CEMENT NULL

Not only this eg. there are lot to do with CASE in SQL.