Showing just one row - 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

Showing just one row

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2011-10-13 : 15:55:41

Hi,

I am new to SQL and I have the following script:

select distinct(o.order_id),
o.product_id,
(CASE c.description WHEN 'Process Requests' THEN
f.method END) [Process Requests],
(CASE c.description WHEN 'Process Alerts' THEN
f.method END) [Process Alerts]
into #temp_a
from orders o with(nolock),
product_mapping m with(nolock),
f_method f with(nolock),
category c with(nolock)
where o.order_id = m.order_id and
f.method_id = m.method_id and
c.category_id = m.category_id and
o.Order_ID = 12345

order by order_id asc

select * from #temp_a(nolock)

The output is

order_id product_id Process requests Process Alerts
12345 ABC NULL Mail
12345 ABC Mail NULL

The required output is :

order_id product_id Process requests Process Alerts
12345 ABC Mail Mail

Thanks in advance,
Petronas

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-13 : 16:26:18

Here's one way:

(nolock is unnecessary on a #temp table)

select order_id, product_id, max([process requests]) as [process Requests], max([process Alerts]) as [process alerts]
from #temp_a
group by order_id, product_id

Be One with the Optimizer
TG

Petronas
Posting Yak Master

134 Posts

Posted - 2011-10-13 : 16:45:10

Thanks TJ! It worked perfectly. Appreciate your help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources