Need help in how to use CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help in how to use CASE

Author

Topic

tlk072107
Starting Member

4 Posts

Posted - 2012-06-27 : 11:33:52

I am relatively new to sql, and I am trying to write a query that requires (I believe) the use of the CASE clause but I am not sure how to put this in.

What I am doing is returning from a table 3 columns, A, B and C. In Column C I will get back one of two values from the table.

I do not want my results to show these values, but rather two different values I assign.

How do I do this?

So I will have something like this:

SELECT [A], COUNT (A) as ColumnName
,[B] as ColumnName
,[C]
FROM [table]
WHERE OrderId = '##########'
Group By A, B, C

And I need to change each of the two possible integer values returning from column C into text values I assign.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-27 : 11:44:23

Something like this:

SELECT
	[A],
	COUNT(A) AS ColumnName,
	[B] AS ColumnName,
	CASE 
		WHEN [C] = 0 THEN 'Zero'
		WHEN [C] = 1 THEN 'One'
	END
FROM
	[table]
WHERE
	OrderId = '##########'
GROUP BY
	A,
	B,
	CASE 
		WHEN [C] = 0 THEN 'Zero'
		WHEN [C] = 1 THEN 'One'
	END

If there could be nulls or other values in column C, you would want to add an else condition to the CASE expression

tlk072107
Starting Member

4 Posts

Posted - 2012-06-27 : 11:57:45

This works perfectly, thank you! I will always have one of those two values for C, so no need for ELSE.

Where do I name Column C, does this occur after "CASE"?

quote:
Originally posted by sunitabeck

Something like this:
SELECT
[A],
COUNT(A) AS ColumnName,
[B] AS ColumnName,
CASE
WHEN [C] = 0 THEN 'Zero'
WHEN [C] = 1 THEN 'One'
END
FROM
[table]
WHERE
OrderId = '##########'
GROUP BY
A,
B,
CASE
WHEN [C] = 0 THEN 'Zero'
WHEN [C] = 1 THEN 'One'
END
If there could be nulls or other values in column C, you would want to add an else condition to the CASE expression

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-27 : 12:56:02

You are very welcome .) To name the column, add an alias after the END keyword of the CASE EXPRESSION.

...
	[B] AS ColumnName,
	CASE 
		WHEN [C] = 0 THEN 'Zero'
		WHEN [C] = 1 THEN 'One'
	END  AS NewColumnName
FROM
....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources