Merging 2 (nearly) identical rows into 1?? - 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 2005 Forums

Transact-SQL (2005)

Merging 2 (nearly) identical rows into 1??

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2007-04-09 : 10:53:04

I have a query that returns sales info for two particular items we sell. The catch is that these 2 items sort of go hand in hand with each other so 90% of the time, the customer will purchase both of these items.

So currently, my query returns 2 rows per customer, 1 row for each product & I want to merge these 2 rows into a single row.

Current Example:
-------------------------------------------------------
Name | Addr1 | City | Item | Qty
-------------------------------------------------------
Joe D. | 22 My St. | Anytown | Red Widget | 2
Joe D. | 22 My St. | Anytown | Blue Widget | 3
Amy S. | 33 Sql Ave. | HerCity | Red Widget | 7
Amy S. | 33 Sql Ave. | HerCity | Blue Widget | 1

Want to create:
--------------------------------------------------------------------
Name | Addr1 | City | Red Widget Qty | Blue Widget Qty
--------------------------------------------------------------------
Joe D. | 22 My St. | Anytown | 2 | 3
Amy S. | 33 Sql Ave. | HerCity | 7 | 1

My current query is a basic query that looks something like this (lets say the product ID of the red widget is 1 & the blue widget is 9):
SELECT
c.name,
c.addr1,
c.city,
p.productName,
p.qty
FROM
customers c
INNER JOIN orders o ON o.orderCustomerID = c.customerID
INNER JOIN products p ON p.productID = o.orderProductID
WHERE
p.productID = 1 OR p.productID = 9

How could I merge these 2 rows together?

Thanks.

-Goalie35

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 10:59:06

[code]select
...
sum(case Item when 'Red Widget' then qty else 0 end) as [Red Widget],
sum(case Item when 'Blue Widget' then qty else 0 end) as [Blue Widget]
From
....
Group By
c.name,
c.addr1,
c.city[/code]

I am sure you can fill in the rest of the necessaries.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 11:01:36

[code]
select name, addr1, city, productname,
sum(case when productID = 1 then Qty else 0 end) as [Red Widget Qty],
sum(case when productID = 9 then Qty else 0 end) as [Blue Widget Qty]
from
(
SELECT c.name, c.addr1, c.city, p.productName, p.qty
FROM customers c
INNER JOIN orders o ON o.orderCustomerID = c.customerID
INNER JOIN products p ON p.productID = o.orderProductID
WHERE p.productID = 1 OR p.productID = 9
) a
group by name, addr1, city, productname
[/code]

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 11:02:06

KH

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 11:03:49

quote:
Originally posted by khtan

KH

Ummm....!! Too much spoon-feeding, KH?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 11:04:55

quote:
Originally posted by harsh_athalye

quote:
Originally posted by khtan

KH

Ummm....!! Too much spoon-feeding, KH?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Yes. You are right. I would be slightly faster if not for typing out all those other codes.

KH

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2007-04-09 : 15:50:52

Thanks for the help so far guys.

I forgot to mention 1 additional item that needs to be merged from 2 rows down to 1 in this query (I thought it would be the same process as merging quantity totals but it's not). I need to include the box # that the item shipped in (VARCHAR data type), which can be different for each item.

So ultimately, my results would need to look like this:
--------------------------------------------------------------------
Name | Addr1 | City | Red Qty | Blue Qty| Red Box # | Blue Box #
--------------------------------------------------------------------
Joe D. | 22 My St. | Anytown | 2 | 3 | AD33922 | UA22001
Amy S. | 33 Sql Ave. | HerCity | 7 | 1 | BA22090 | BC34373

I'm having problems merging these since these box #'s are varchar data types and cannot use "SUM" to group them. How can I do this?

Thanks again.

-Goalie35

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 18:22:41

use max()

KH

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-09 : 22:13:16

... and if there are 3 or more items?

--Jeff Moden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 23:00:42

do this in the front end. Use csv etc.

KH

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-10 : 01:24:11

I hate changing requirements!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-10 : 06:49:18

quote:
Originally posted by Jeff Moden

... and if there are 3 or more items?

--Jeff Moden

This is my favourite question

After giving solution, questions get back to us saying "No. There are actually more than 2 items"

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources