Case Pivot What? - 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 Pivot What?

Author

Topic

Mr Butter
Starting Member

3 Posts

Posted - 2011-07-16 : 09:58:06

I'm trying to get the following to work without a subquery. The results I am getting are separate rows. Any help will greatly be appreciated. Using t-sql.

id phone# Type
1 223 Mbl
1 224 Hom
2 333 Hom
2 null Mbl

undesired result
Id Mobile Home
1 223
1 224
2 null null
2 333

Desired result
id Mobile Home
1 223 224
2 null 333

select ID,
(case type when 'hom' then phone# end) as Home,
(case type when 'mbl' then phone# end) as Mobile
from table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-16 : 10:21:53

[code]
select ID,
max(case type when 'hom' then phone# end) as Home,
max(case type when 'mbl' then phone# end) as Mobile
from table
group by ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-16 : 10:54:08

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

There is no such thing as a magical generic “id”, we never, never use anything but Latin-1 alphas, digits and underscore in data element names, etc.

>> I am trying to get the following to work without a subquery. <<

Why do you care HOW something is done and NOT what is done?

Let's do your job for you, starting with a skeleton DDL

CREATE TABLE Employee_Telephones
(emp_id INTEGER NOT NULL,
phone_type CHAR(3) NOT NULL
CHECK (phone_type IN ('HOM', 'MBL')),
PRIMARY KEY (emp_id, phone_type),
phone_nbr CHAR(18) NOT NULL, --- international standards
);

SELECT emp_id,
MAX(CASE phone_type WHEN 'HOM' THEN phone_nbr ELSE NULL END)
AS home_phone_nbr,
MAX (CASE phone_type WHEN 'MBL' THEN phone_nbr ELSE NULL END)
AS mobile__phone_nbr,
FROM Employee_Telephones
GROUP BY emp_id;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 00:15:32

Seems KH understood the problem just fine. Maybe he's a mind reader.

Or maybe, he'd rather help people instead of rag on them as you do.

Tell ya what Joe, you stop bashing newbies, and I'll stop checking all your posts for the unmistakable smell of doo-doo.

Because every time I smell it, I'm going to point it out. Oops, think I just stepped in some.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources