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

SQL Server 2005 Forums

Transact-SQL (2005)

Case Question

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2009-01-13 : 14:57:42

I have the following Case Statement. I'm taking a field called service code and making it 3 seperate colums

CASE WHEN Service.[Svc Code] ='Cable' THEN 'Cable' Else ' ' END),
(CASE WHEN Service.[Svc Code] ='HSD' THEN 'HSD' Else ' ' END),
(CASE WHEN Service.[Svc Code] ='Phone' THEN 'Phone' Else ' ' END)

If I have a customer that has all 3 then it will give me 3 seperate lines. Even if I Group By. I know I've done this before successfully but I must be having a bad day Is anyone able to shed some light ?

Everything is the same but these fields. This is the same Customer
Phone
HSD
Cable

bismarkcount
Starting Member

15 Posts

Posted - 2009-01-13 : 15:12:28

why not use a distinct on the customer?
it will only show one... your question is not very clear though

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-13 : 15:16:32

you'll have to do something like

[CODE]
SELECT CUSTOMER_ID,
MAX(CASE WHEN Service.[Svc Code] ='Cable' THEN 'Cable' Else ' ' END),
MAX(CASE WHEN Service.[Svc Code] ='HSD' THEN 'HSD' Else ' ' END),
MAX(CASE WHEN Service.[Svc Code] ='Phone' THEN 'Phone' Else ' ' END)
FROM MY_TABLE
GROUP BY CUSTOMER_ID
[/CODE]

ZMike
Posting Yak Master

110 Posts

Posted - 2009-01-13 : 15:21:02

Bismarkcount,

I do have a Select Distinct

SELECT DISTINCT Service.[Account Number] ,
(CASE WHEN Service.[Svc Code] ='Cable' THEN 'Cable' Else ' ' END),
(CASE WHEN Service.[Svc Code] ='HSD' THEN 'HSD' Else ' ' END),
(CASE WHEN Service.[Svc Code] ='Phone' THEN 'Phone' Else ' ' END)

From dbo.Service
WHERE (Service.[Svc Code] IN ('Cable','HSD','Phone'))

Group By Service.[Account Number] ,Service.[Svc Code] **** Or I've also tried the case statment here as well

It returns

Account Cable HSD PHONE

Customer 1 Phone
Customer 1 HSD
Customer 1 Cable

What I'd like is

Customer 1 Cable HSD Phone

Does that make sense ?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-13 : 15:25:19

[CODE]
SELECT SERVICE.[ACCOUNT NUMBER],
MAX(CASE
WHEN SERVICE.[SVC CODE] = 'Cable' THEN 'Cable'
ELSE ''
END),
MAX(CASE
WHEN SERVICE.[SVC CODE] = 'HSD' THEN 'HSD'
ELSE ''
END),
MAX(CASE
WHEN SERVICE.[SVC CODE] = 'Phone' THEN 'Phone'
ELSE ''
END)
FROM DBO.SERVICE
WHERE SERVICE.[SVC CODE] IN ('Cable','HSD','Phone')
GROUP BY SERVICE.[ACCOUNT NUMBER]
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources