combining multiple rows into one

Question

combining multiple rows into one

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

zypsy (10/6/2008)
Based on some criteria, I have reduced the number of orders in the dataset
from about 42000 to about 6000.

Then, methinks that something is drastically wrong... you just said (and I agree), that most customers will buy one thing in any given day. According to your stats, that's an average of 7 things per day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

zypsy SSC Veteran Points: 249 More actions · Answer 2

sorry, if i've confused you.

customers can and do have multiple items in an order. but customers won't have more than one order in a day.

the data reduction has been done by removing orders that are buybacks/exchanges, and orders that have a single item.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

zypsy (10/6/2008)
i have got the table i want by running something like
SELECT billdate,custcode,custname,
MAX(CASE WHEN Seq=1 THEN brand ELSE NULL END) AS brand,
MAX(CASE WHEN Seq=1 THEN item ELSE NULL END) AS item,
MAX(CASE WHEN Seq=1 THEN cat ELSE NULL END) AS cat,
MAX(CASE WHEN Seq=2 THEN brand ELSE NULL END) AS brand2,
MAX(CASE WHEN Seq=2 THEN item ELSE NULL END) AS item2,
.
.
.
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY billdate,custcode,custname
ORDER BY billdate,custcode,custname) AS Seq, *
FROM MyTable)t
GROUP BY billdate,custcode,custname

You're on the right track with that. The only concern I have is the assumed maximum of 5 items per customer per "day". As soon as you assume that, someone else will buy 9 or 10.

Since you can't get the test data we actually need to test code with, I'll see if I can make some so that it's all handled dynamically without regard to the number of purchase by a given customer on a given day... if you think you need it. Lemme know, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)