combining rows - 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

combining rows

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-28 : 13:24:14

I am trying to combine some rows, but having a difficult time, as dates tend to default into 1/1/1900.
I have rows that look like this:
id,part,price,date,price,date,price,date
1,aa,0,1/1/1900,50,4/30/2010,0,1/1/1900
1,aa,0,1/1/1900,0,1/1/1900,50,4/30/2010
1,aa,50,5/1/2010,0,1/1/1900,0,1/1/1900

I need to combine these to 2 rows as follows:
id,part,price,date,price,date,price,date
1,aa,0,1/1/1900,50,4/30/2010,50,4/30/2010
1,aa,50,5/1/2010,0,1/1/1900,0,1/1/1900

I tried using sum() on the price columns and group by id,part,dates but it didn't change anything.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-28 : 13:30:45

is this what you're trying to achieve?

select a.id, a.part, a.price, a.date, sum(b.price), max(b.date), max(c.price), max(c.date)
from tab1 a join tab2 b on a.id = b.id join tab3 c on a.id = c.id
group by a.id, a.part, a.price, a.date

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-28 : 13:40:24

I probably should have been a little more detailed.

The rows are all contained in 1 table

The query I have so far just picks prices that fall within 3 days of the current day. I need to modify it so it can combine products that have prices on same dates.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 13:53:56

Post a same of the raw data as it is in the table, and a sample of what you expect to be returned by the query. And please, use some spaces between the values. Reading your first sample hurt my eyes. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-28 : 14:31:17

Alright, the table and first query are the exact same (the first query is a select * of the table).
id , part , price1 , date1 , price2 , date2 , price3 , date3
1 , aa , 0 , 1/1/1900 , 50 , 4/30/2010 , 0 , 1/1/1900
1 , aa , 0 , 1/1/1900 , 0 , 1/1/1900 , 50 , 4/30/2010
1 , aa , 50 , 5/1/2010 , 0 , 1/1/1900 , 0 , 1/1/1900

Since part aa has price2 and price3 with the same date, there is no point in displaying 2 rows, as they could be combined. The second query I am trying to make should return results like this:
id , part , price1 , date1 , price2, date2 , price3 , date3
1 , aa , 0 , 1/1/1900, 50 , 4/30/2010 , 50 , 4/30/2010
1 , aa , 50 , 5/1/2010, 0 , 1/1/1900 , 0 , 1/1/1900

Product aa contains 2 rows instead of the original 3. I am summing the prices according to dates and products, but each different date needs its own row.

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:14:45

What happens if the number of rows are more than 2 where price2 and price3 are with the same date.

PBUH

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-28 : 16:24:54

If price2 and price3 have the same date, then all corresponding price2 values will be summed and same with price3.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 16:33:19

So this:

1 , aa , 0 , 1/1/1900, 50 , 4/30/2010 , 50 , 4/30/2010
1 , aa , 0 , 1/1/1900, 0 , 4/30/2010 , 500 , 4/30/2010

Would return:

1 , aa , 0 , 1/1/1900, 50 , 4/30/2010 , 550 , 4/30/2010

yes?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-28 : 17:01:05

Yes, that is correct.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 19:26:18

This one was a lot more complex than I first gave it credit for. For a start, the fact that many of the dates were populated with 1/1/1900 made thing difficult. So I decided to strip those out, replacing them with nulls. Then there's some FULL OUTER JOINs with some complex ON clauses, etc.

;WITH cte AS (
	SELECT id, part, price1, price2, price3,
		CASE WHEN date1 = '19000101' THEN NULL ELSE date1 END AS date1,
		CASE WHEN date2 = '19000101' THEN NULL ELSE date2 END AS date2,
		CASE WHEN date3 = '19000101' THEN NULL ELSE date3 END AS date3
	FROM tableName
)

SELECT COALESCE(c1.id, c2.id, c3.id) AS id, 
	COALESCE(c1.part, c2.part, c3.part) AS part, 
	COALESCE(price1, 0), 
	COALESCE(date1, '19000101'),
	COALESCE(price2, 0),  
	COALESCE(date2, '19000101'), 
	COALESCE(price3, 0), 
	COALESCE(date3, '19000101')
FROM (	SELECT id, part, date1, SUM(price1) AS price1
		FROM cte
		GROUP BY id, part, date1 ) c1
FULL OUTER JOIN (
		SELECT id, part, date2, SUM(price2) AS price2
		FROM cte
		GROUP BY id, part, date2 ) c2
	ON c1.id = c2.id
	AND c1.part = c2.part
	AND c1.date1 = COALESCE(c2.date2, c1.date1)
	AND c2.date2 IS NOT NULL
FULL OUTER JOIN (
		SELECT id, part, date3, SUM(price3) AS price3
		FROM cte
		GROUP BY id, part, date3 ) c3
	ON (COALESCE(c1.id, c2.id) = c3.id
		AND COALESCE(c1.part, c2.part) = c3.part
		AND c2.date2 = COALESCE(c3.date3, c2.date2)
		AND c3.date3 = COALESCE(c2.date2, c3.date3)
	)
	OR ( c1.id = c3.id 
		AND c1.part = c3.part
		AND c1.date1 = COALESCE(c3.date3, c1.date1)
		AND c3.date3 IS NOT NULL 
	)
WHERE COALESCE(c1.date1, c2.date2, c3.date3) IS NOT NULL
ORDER BY id, part, COALESCE(c1.date1, c2.date2, c3.date3)

Sorry it's not very pretty. Maybe someone else can simplify it.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 06:58:04

Here's a much simpler version of the above query. Isn't it funny how you can look at something the following morning, and say to yourself "OMG, what was I thinking??? Why the hell did I do it that way?"

Please make sure you test this across a wide variety of test data to ensure it meets your needs.

;WITH cte AS (
	SELECT id, part, price1, price2, price3,
		CASE WHEN date1 = '19000101' THEN NULL ELSE date1 END AS date1,
		CASE WHEN date2 = '19000101' THEN NULL ELSE date2 END AS date2,
		CASE WHEN date3 = '19000101' THEN NULL ELSE date3 END AS date3
	FROM tableName
)

SELECT COALESCE(c1.id, c2.id, c3.id) AS id, 
	COALESCE(c1.part, c2.part, c3.part) AS part, 
	COALESCE(price1, 0), 
	COALESCE(date1, '19000101'),
	COALESCE(price2, 0),  
	COALESCE(date2, '19000101'), 
	COALESCE(price3, 0), 
	COALESCE(date3, '19000101')
FROM (	SELECT id, part, date1, SUM(price1) AS price1
		FROM cte
		GROUP BY id, part, date1 ) c1
FULL OUTER JOIN (
		SELECT id, part, date2, SUM(price2) AS price2
		FROM cte
		GROUP BY id, part, date2 ) c2
	ON c1.id = c2.id
		AND c1.part = c2.part
		AND c1.date1 = c2.date2 
FULL OUTER JOIN (
		SELECT id, part, date3, SUM(price3) AS price3
		FROM cte
		GROUP BY id, part, date3 ) c3
	ON  c3.id IN (c1.id, c2.id)
		AND c3.part IN (c1.part, c2.part)
		AND c3.date3 IN (c1.date1, c2.date2)
WHERE COALESCE(c1.date1, c2.date2, c3.date3) IS NOT NULL
ORDER BY id, part, COALESCE(c1.date1, c2.date2, c3.date3)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-29 : 07:26:43

This is bad table design. I take it the table is a heap?

Do you have any key that can uniquely identify a row in the table? If you did this would be a lot easier and much more performant. Why 3 date / price pairs? why not have a normalised table with columns for price | date | description if these model different types of payments.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-04-29 : 16:59:10

It's not necessarily different payments. This table is a combination of 5 tables and 3 different vb.net programs pulling website info. It's so bloated that redesigning to something 'normal' isn't worth doing.

On to the code, DBA I tried to adapt most of what was written, but couldn't run the whole thing together. I chopped bits and pieces, and then finally ended up creating 3 temp tables (one for each pair), using the code of getting rid of the dates. When combining all 3 temp tables back together, the dates did not cause a problem anymore.

Glad that it's solved, thanks for the help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-30 : 04:17:58

quote:

It's not necessarily different payments. This table is a combination of 5 tables and 3 different vb.net programs pulling website info. It's so bloated that redesigning to something 'normal' isn't worth doing.

I think we'll all have been in that situation at some point or other. Glad you got it sorted.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources