April 15, 2010 at 4:07 pm
Hi all:
I recieve a flat file from our company that list customer orders. I am using SSIS to import the Orders into SQL Server. Each record has a unique BusinessKey. If a customer has more than one order they are listed multipe times one for each order.
I need to list all of the ProductCodes for a customer in one record. eg
Turn this
'123456789','Jones,Tammy','789456','TJ1234','04/15/2010','P550'
'123456789','Jones,Tammy','789456','TJ1234', '04/15/2010','CAZN'
'123456789','Jones,Tammy','789456','TJ1234', '04/15/2010', 'CATO'
Into this
'123456789','Jones,Tammy','789456','TJ1234','04/15/2010','P550', 'CAZN', 'CATO'
At the end of this process this is sent to a flat file to be consumed by another legacy system.
What is the best way to update a customer so that all of the orders are in one record?
Any help would be appreciated.
Here are my test tables and data
--Create test table
IF OBJECT_ID('OrdersTest') IS NOT NULL
DROP TABLE OrdersTest
GO
CREATE TABLE OrdersTest
(
BusinessKey NVARCHAR(9)
,Name NVARCHAR (30)
,OrderNo NVARCHAR(6)
,Account NVARCHAR(7)
,OrderDate DATETIME
,ProductCode NVARCHAR(9)
)
--Create test data
INSERT INTO OrdersTest VALUES ('123456789','Jones,Tammy','789456','TJ1234','04/15/2010','P550')
INSERT INTO OrdersTest VALUES ('123456789','Jones,Tammy','789456','TJ1234', '04/15/2010','CAZN')
INSERT INTO OrdersTest VALUES ('123456789','Jones,Tammy','789456','TJ1234', '04/15/2010', 'CATO')
INSERT INTO OrdersTest VALUES ('234567889','Davis,Miles','321654','MD9876','04/15/2010', 'P8786')
INSERT INTO OrdersTest VALUES ('651421889','Coltrane,John','258852','JC5689','04/15/2010','P9780')
INSERT INTO OrdersTest VALUES ('967852421','McCartney,Paul','589865','PM8989', '04/15/2010','P8759')
INSERT INTO OrdersTest VALUES ('897655382','John,Elton','659875','EJ548','04/15/2010','P878')
INSERT INTO OrdersTest VALUES ('345548897','Basie,William','588796','CB2165','04/15/2010','P880')
INSERT INTO OrdersTest VALUES ('345548897','Basie,William','588796','CB2165','04/15/2010','CMAP')
INSERT INTO OrdersTest VALUES ('345548897','Basie,William','588796','CB2165','04/15/2010','CBCC')
INSERT INTO OrdersTest VALUES ('545567889','Jordan,Duke','432567','DJ699','04/15/2010','P750')
INSERT INTO OrdersTest VALUES ('769854523','Dorsey,Tommy','123456','TD7589','04/15/2010','P880')
INSERT INTO OrdersTest VALUES ('769854523','Dorsey,Tommy','123456','TD7589','04/15/2010','CZOP')
INSERT INTO OrdersTest VALUES ('769854523','Dorsey,Tommy','123456','TD7589','04/15/2010','CMET')
--Return all records
SELECT * FROM OrdersTest
/**********************************************
Select Customers with more than one (1) productcode
**********************************************/
SELECT BusinessKey, COUNT(*) MoreThan1Product
FROM OrdersTest
GROUP BY BusinessKey
HAVING COUNT(BusinessKey) > 1
-- Table for customers with MultipleProductCodes
CREATE TABLE CustomerWithMultipleProducts
(
BusinessKey NVARCHAR(9)
,Name NVARCHAR (30)
,OrderNo NVARCHAR(6)
,Account NVARCHAR(7)
,OrderDate DATETIME
,ProductCode1 NVARCHAR(9)
,ProductCode2 NVARCHAR(9)
,ProductCode3 NVARCHAR(9)
,ProductCode4 NVARCHAR(9)
,ProductCode5 NVARCHAR(9)
,ProductCode6 NVARCHAR(9)
,ProductCode7 NVARCHAR(9)
,ProductCode8 NVARCHAR(9)
)
--DROP TABLE OrdersTest
--DROP TABLE CustomerWithMultipleProducts
April 15, 2010 at 4:18 pm
April 15, 2010 at 4:24 pm
Give this code a test spin:
SELECT DISTINCT
ot.BusinessKey,
ot.Name,
ot.OrderNo,
ot.OrderDate,
STUFF(( SELECT
', ' + ot1.ProductCode
FROM
dbo.OrdersTest ot1
WHERE
ot1.OrderNo = ot.OrderNo
FOR XML PATH('')), 1, 2, '') as ProductCodes
FROM
dbo.OrdersTest ot
April 15, 2010 at 4:30 pm
If the data need to be stored in the intermediate table in separate columns I'd use the CrossTab approach as described in the related link in my signature.
But if the purpose is to build a csv for export to a flat file I'd use Lynn's solution.
CrossTab solution:
;WITH cte AS
(
SELECT
BusinessKey ,Name ,OrderNo,Account ,OrderDate ,ProductCode,
ROW_NUMBER() OVER(PARTITION BY BusinessKey ORDER BY ProductCode ) AS ROW
FROM OrdersTest
)
SELECT
BusinessKey ,Name ,OrderNo,Account ,OrderDate ,
MAX( CASE WHEN ROW=1 THEN ProductCode ELSE '' END ) AS ProductCode1,
MAX( CASE WHEN ROW=2 THEN ProductCode ELSE '' END ) AS ProductCode2,
MAX( CASE WHEN ROW=3 THEN ProductCode ELSE '' END ) AS ProductCode3,
MAX( CASE WHEN ROW=4 THEN ProductCode ELSE '' END ) AS ProductCode4,
MAX( CASE WHEN ROW=5 THEN ProductCode ELSE '' END ) AS ProductCode5,
MAX( CASE WHEN ROW=6 THEN ProductCode ELSE '' END ) AS ProductCode6,
MAX( CASE WHEN ROW=7 THEN ProductCode ELSE '' END ) AS ProductCode7,
MAX( CASE WHEN ROW=8 THEN ProductCode ELSE '' END ) AS ProductCode8
FROM cte
GROUP BY BusinessKey ,Name ,OrderNo,Account ,OrderDate
April 15, 2010 at 4:34 pm
My concern with the intermediate table, what happens the first time a customer orders more than 8 products? If you only have 8 products, what happens when you add a 9th or 10th?
April 15, 2010 at 4:39 pm
Lynn Pettis (4/15/2010)
My concern with the intermediate table, what happens the first time a customer orders more than 8 products? If you only have 8 products, what happens when you add a 9th or 10th?
That's what I was thinking too and I was hoping that a comma delimited list was all that was needed. Seems even if the data is needed in the SQL Server for something else it can be left there and used there in its non-compacted form and the XML PATH process would be just for building the file to load the other system.
April 15, 2010 at 4:52 pm
If there is the need to store more than 8 products, I'd question the purpose of CustomerWithMultipleProducts, too.
In that case I'd most probably use that table to store the unique information only (would be a lot closer to normalization anyway...) and query the multiple products from OrdersTest if needed, either with FOR XML PATH or DynamicCrossTab.
April 16, 2010 at 7:40 am
The flat file's only purpose is consumption by the legacy system. I am performing other processing in SQL Server that I did not list. Such as calculating Order totals, spliting the names into first and last, setting bits based on the product code mix for shipping methods etc.
Thanks for your reply!
April 16, 2010 at 7:57 am
Lynn, thanks for the response I like your approach. Thanks for the links to the articles. In my situation I think I will you the CTE approach presented by Lutz. There is additionally processing that has to be done once all of the codes are in one row.
Thanks again.
April 16, 2010 at 8:00 am
Lutz, thanks for your reply. I think your approach will work best for my situation. I need the Product codes in separate columns to do some additional processing for comsumption by a web service also. I do understand your concerns about the additional prodcut codes(There are actually 14 I have to account for now) but given the constraints I have to work with this is the best approach for now.
Thanks for your reply
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply