Case Statement - 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

Case Statement

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-07 : 11:42:55

This query prodcues the below:

select area = coalesce(area, 'Balto CLMS Take UN'),
count(p.fo) as cnt
from pendingbyddsandfo p
join natdocfile n on n.doc = p.fo
group by n.area


Areas     Totals
CLMSUnit    3
01	   17
02	  168
03	   62
04	   32
05	   36
06	   96

How do I change my stored procedure to produce this instead with a Case statement?


Areas         Total
CLMSUnit	3
Area 1	       17
Area 2	      168
Area 3	       62
Area 4	       32
Area 5	       36
Area 6	       96

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 11:46:29

do you have a field which has these area descriptions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 11:48:25

if yes it should be


select area = coalesce(areadescription, 'Balto CLMS Take UN'),
count(p.fo) as cnt
from pendingbyddsandfo p
join natdocfile n on n.doc = p.fo
group by n.areadescription

if not


select area = coalesce('Area ' + cast(n.area as varchar(3)), 'Balto CLMS Take UN'),
count(p.fo) as cnt
from pendingbyddsandfo p
join natdocfile n on n.doc = p.fo
group by 'Area ' + cast(n.area as varchar(3))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-07 : 11:51:05

Thanks I don't have a field with those descriptions, so I used the last version. How can I get rid of the 0 in front of the number?

Areas Totals
CLMSUnit 3
Area 01 17
Area 02 168
Area 03 62
Area 04 32
Area 05 36
Area 06 96

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 12:04:23

quote:
Originally posted by JJ297

Thanks I don't have a field with those descriptions, so I used the last version. How can I get rid of the 0 in front of the number?

Areas Totals
CLMSUnit 3
Area 01 17
Area 02 168
Area 03 62
Area 04 32
Area 05 36
Area 06 96


select area = coalesce('Area ' + cast(n.area*1 as varchar(3)), 'Balto CLMS Take UN'),
count(p.fo) as cnt
from pendingbyddsandfo p
join natdocfile n on n.doc = p.fo
group by 'Area ' + cast(n.area*1 as varchar(3))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-07 : 12:04:33

Maybe this will help (notice I provided some sample data in a consumable format!)

DECLARE @Yak TABLE (Area VARCHAR(20), Total INT)
INSERT @Yak (Area, Total)
VALUES
('CLMSUnit',    3),
('01',	   17),
('02',	  168),
('03',	   62),
('04',	   32),
('05',	   36),
('06',	   96)


SELECT *
FROM @Yak



SELECT 
    CASE 
        WHEN Area NOT LIKE '%[^0-9]%' 
            THEN 'Area ' + CAST(CAST(Area AS INT) AS VARCHAR(10)) 
        ELSE Area
    END AS Areas,
    Total
    
FROM @Yak

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-07 : 12:56:22

Thanks visakh16 that worked. Please explain what this is doing especially the *1

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-07 : 13:09:28

Thanks Lamprey for your reply as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 23:51:06

quote:
Originally posted by JJ297

Thanks visakh16 that worked. Please explain what this is doing especially the *1

* 1 was just to make the values 01,02 etc to integer to strip off leading 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-08 : 10:39:52

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 10:46:29

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources