divide a state into different formats? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

divide a state into different formats?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-08 : 15:15:38

i have a state column and status column in customer table in the following way:

customer state status
john AK regular
james AK null
jack TX null
jerry TX regular

how can I group by a state in such a way that the states which has status regular shows up as separate state AK-regular rather than AK.

select count(name),(case when Status is null then state end) as State from Customer
group by (case when status is null then state end)

using the above query i getting the State name is null..How can i get it as AK-regular
Thank you

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-08 : 15:50:41

Give us an example of the output you are expecting.

-Chad

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-08 : 20:47:23

Thanks for your response ChadMat..

The output should be like this

State Count
AK 1
AK-regular 1
TX 1
TX-regualr 1

Thank you

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-09 : 02:29:41

SELECT State + case when status is null then '' ELSE ' - ' + status end as State, COUNT(1) as count
FROM Customer
GROUP BY State + case when status is null then '' ELSE ' - ' + status end

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 13:01:00

its equivalent to


SELECT State + COALESCE('-' + status,''),COUNT(1) AS Cnt
FROM  Customer
GROUP BY State + COALESCE('-' + status,'')

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-11 : 11:09:53

Thank you CHadmat and Visakh16 for your guidance...It helped me a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 11:12:40

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources