combining multiple rows into one

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)

combining multiple rows into one

Author

Topic

zypsy

6 Posts

Posted - 2008-10-05 : 02:20:26

i have this sales data for a retail shop that's pretty basic/primitive.

each row is for an item, and not for an order. so if a customer purchases 3 items in a single order, there are 3 separate rows in the data. i would like to combine these rows in a single row that represents an order, for doing market basket analysis.

customers can be uniquely identified by combining "customer code" and "customer name". a SINGLE order can be identified by combining "customer code", "customer name", and "bill date"

columns that need to be in the final table are bill date, customer code, customer name, brand, item name, category, and subcategory.

all columns from brand---subcategory need to be renamed and included/repeated for all items in a single purchase by a customer. for example, if an order has 3 items, the columns should be

billdate custcode custname brand item cat subcat brand2 item2 cat2 subcat2 brand3 item3 cat3 subcat3

i'm thinking of a left outer join, but i'm still not getting it. can someone please help me here?

thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 02:28:03

before i gave you a solution let me ask one thing. how can you be sure about maximun number of items thats included in a order? wouldnt it be better if you return the brand,item etc info as comma seperated values?

zypsy

6 Posts

Posted - 2008-10-05 : 11:50:09

All items in a single order will be added in a single row. All items in a single order wil be identified by "customer code"+"customer name"+"bill date". I don't need to know the maximum # of items in an order, that will be automatically taken care of when i add items from rows where "customer code" and "customer name" and "bill date" are ALL equal.

The market basket analysis will answer something like, if an order has A what is the probability of the order having B item too? or B and C? or B and C and D?....For this, I will be creating additional columns that will calculate the SUPPORT and CONFIDENCE for each rule. I can do this easily in MS Excel.

I just need each row in the dataset to represent a single order/purchase along with info on all the items in the order.

thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 13:32:23

try something like this

SELECT custcode,custname,billdate,
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,
MAX(CASE WHEN Seq=2 THEN  cat ELSE NULL END) AS cat2,
MAX(CASE WHEN Seq=3 THEN  brand ELSE NULL END) AS brand3,
MAX(CASE WHEN Seq=3 THEN  item ELSE NULL END) AS item3,
MAX(CASE WHEN Seq=3 THEN  cat ELSE NULL END) AS cat3
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY custcode,custname,billdate ORDER BY PK) AS Seq, *
FROM YourTable)t
GROUP BY custcode,custname,billdate

zypsy

6 Posts

Posted - 2008-10-06 : 02:54:06

thanks a LOT!!! i got it now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 03:09:05

quote:
Originally posted by romakanta

thanks a LOT!!! i got it now.

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources