Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query/Table cleanup

Status
Not open for further replies.

sue0912

MIS
Jun 19, 2007
50
US
Hi Everyone,

I need a recommondation on where to start. I have 2 tables. Table 1 is based with Customers, Product Lines and Amount Sold. Table 2 is based with Customers, and Machines.

Now both tables have customers listed multiple times...(meaning they can have purchased several different product lines and also own several machines)

Now I have been asked to compile a report that will pivot out the product lines grouped by customer but also list the machines that they own.

I have been monkeying around with queries but keep coming back to the tables as my queries are not coming out correctly.

so...my question is how would recommend cleaning these tables up so write a query.

Let me know if any code examples would help.

Sue
 
What version of SQL? if 2005 or greater there is a PIVOT function built in.
 
I have 2000. So I started with Table 1. I wrote the following query to "pivot" the product lines.

DROP Table temp_prod_codes
CREATE TABLE temp_prod_codes(division char(2), Customer nvarchar(100), prod_code char(4), qty float, ext_cost money, Tsold money) INSERT INTO temp_prod_codes (division, customer, prod_code, qty, ext_cost, tsold) SELECT temp_parts.lastofdivision, temp_parts.customer, temp_parts.prod_line, temp_parts.qty, temp_parts.ext_cost, temp_parts.ext_amnt FROM Temp_parts WHERE Temp_parts.prod_line IN ('ADPT', 'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE', 'FABR', 'FILT', 'FINL', 'GRID', 'HAZE', 'HOSE', 'HW', 'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 'NSHW', 'NSSC', 'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI', 'ROLL', 'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE', 'TESB', 'TRAC', 'TROM', 'TS', 'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 'WPPG', 'WPPR')
SELECT division, Customer,
SUM(CASE Prod_Code WHEN 'ADPT' THEN TSold ELSE 0 END) AS ADPT,
SUM(CASE Prod_Code WHEN 'BEAR' THEN TSold ELSE 0 END) AS BEAR,
SUM(CASE Prod_Code WHEN 'BELT' THEN TSold ELSE 0 END) AS BELT,
SUM(CASE Prod_Code WHEN 'BOLT' THEN TSold ELSE 0 END) AS BOLT,
SUM(CASE Prod_Code WHEN 'CHAN' THEN TSold ELSE 0 END) AS CHAN,
SUM(CASE Prod_Code WHEN 'CLTH' THEN TSold ELSE 0 END) AS CLTH,
SUM(CASE Prod_Code WHEN 'CPDS' THEN TSold ELSE 0 END) AS CPDS,
SUM(CASE Prod_Code WHEN 'CRBL' THEN TSold ELSE 0 END) AS CRBL,
SUM(CASE Prod_Code WHEN 'DRUM' THEN TSold ELSE 0 END) AS DRUM,
SUM(CASE Prod_Code WHEN 'EAGL' THEN TSold ELSE 0 END) AS EAGL,
SUM(CASE Prod_Code WHEN 'ELEP' THEN TSold ELSE 0 END) AS ELEP,
SUM(CASE Prod_Code WHEN 'ELPS' THEN TSold ELSE 0 END) AS ELPS,
SUM(CASE Prod_Code WHEN 'ENGP' THEN TSold ELSE 0 END) AS ENGP,
SUM(CASE Prod_Code WHEN 'ENPG' THEN TSold ELSE 0 END) AS ENPG,
SUM(CASE Prod_Code WHEN 'EXTE' THEN TSold ELSE 0 END) AS EXTE,
SUM(CASE Prod_Code WHEN 'FABR' THEN TSold ELSE 0 END) AS FABR,
SUM(CASE Prod_Code WHEN 'FILT' THEN TSold ELSE 0 END) AS FILT,
SUM(CASE Prod_Code WHEN 'FINL' THEN TSold ELSE 0 END) AS FINL,
SUM(CASE Prod_Code WHEN 'GRID' THEN TSold ELSE 0 END) AS GRID,
SUM(CASE Prod_Code WHEN 'HAZE' THEN TSold ELSE 0 END) AS HAZE,
SUM(CASE Prod_Code WHEN 'HOSE' THEN TSold ELSE 0 END) AS HOSE,
SUM(CASE Prod_Code WHEN 'HW' THEN TSold ELSE 0 END) AS HW,
SUM(CASE Prod_Code WHEN 'HYPG' THEN TSold ELSE 0 END) AS HYPG,
SUM(CASE Prod_Code WHEN 'HYPS' THEN TSold ELSE 0 END) AS HYPS,
SUM(CASE Prod_Code WHEN 'INER' THEN TSold ELSE 0 END) AS INER,
SUM(CASE Prod_Code WHEN 'KEY' THEN TSold ELSE 0 END) AS KEYS,
SUM(CASE Prod_Code WHEN 'MISC' THEN TSold ELSE 0 END) AS MISC,
SUM(CASE Prod_Code WHEN 'NSHW' THEN TSold ELSE 0 END) AS NSHW,
SUM(CASE Prod_Code WHEN 'NSSC' THEN TSold ELSE 0 END) AS NSSC,
SUM(CASE Prod_Code WHEN 'NUTS' THEN TSold ELSE 0 END) AS NUTS,
SUM(CASE Prod_Code WHEN 'OBSO' THEN TSold ELSE 0 END) AS OBSO,
SUM(CASE Prod_Code WHEN 'OS' THEN TSold ELSE 0 END) AS OS,
SUM(CASE Prod_Code WHEN 'PINS' THEN TSold ELSE 0 END) AS PINS,
SUM(CASE Prod_Code WHEN 'PULL' THEN TSold ELSE 0 END) AS PULL,
SUM(CASE Prod_Code WHEN 'RADI' THEN TSold ELSE 0 END) AS RADI,
SUM(CASE Prod_Code WHEN 'ROLL' THEN TSold ELSE 0 END) AS ROLL,
SUM(CASE Prod_Code WHEN 'SCRE' THEN TSold ELSE 0 END) AS SCRE,
SUM(CASE Prod_Code WHEN 'SEAL' THEN TSold ELSE 0 END) AS SEAL,
SUM(CASE Prod_Code WHEN 'SHAF' THEN TSold ELSE 0 END) AS SHAF,
SUM(CASE Prod_Code WHEN 'SHRP' THEN TSold ELSE 0 END) AS SHRP,
SUM(CASE Prod_Code WHEN 'SPRI' THEN TSold ELSE 0 END) AS SPRI,
SUM(CASE Prod_Code WHEN 'SPRO' THEN TSold ELSE 0 END) AS SPRO,
SUM(CASE Prod_Code WHEN 'STSC' THEN TSold ELSE 0 END) AS STSC,
SUM(CASE Prod_Code WHEN 'STSH' THEN TSold ELSE 0 END) AS STSH,
SUM(CASE Prod_Code WHEN 'TAPE' THEN TSold ELSE 0 END) AS TAPER,
SUM(CASE Prod_Code WHEN 'TESB' THEN TSold ELSE 0 END) AS TESB,
SUM(CASE Prod_Code WHEN 'TRAC' THEN TSold ELSE 0 END) AS TRAC,
SUM(CASE Prod_Code WHEN 'TROM' THEN TSold ELSE 0 END) AS TROM,
SUM(CASE Prod_Code WHEN 'TS' THEN TSold ELSE 0 END) AS TS,
SUM(CASE Prod_Code WHEN 'TSSC' THEN TSold ELSE 0 END) AS TSSC,
SUM(CASE Prod_Code WHEN 'USSC' THEN TSold ELSE 0 END) AS USSC,
SUM(CASE Prod_Code WHEN 'VBEL' THEN TSold ELSE 0 END) AS VBEL,
SUM(CASE Prod_Code WHEN 'WASH' THEN TSold ELSE 0 END) AS WASH,
SUM(CASE Prod_Code WHEN 'WHEL' THEN TSold ELSE 0 END) AS WHEL,
SUM(CASE Prod_Code WHEN 'WPPG' THEN TSold ELSE 0 END) AS WPPG,
SUM(CASE Prod_Code WHEN 'WPPR' THEN TSold ELSE 0 END) AS WPPR

INTO Pivot_Parts
FROM Temp_prod_codes
GROUP BY division, Customer
ORDER BY Customer

Now I am working on how to bring in the machines from Table 2...AND any Customers and Machines that do not exist in Table 1

If anyone gets a chance to read this...can you let me know if (based on the information I have tried to explain) I am on the right track..

Thanks Sue
 
Ok. I added in Table 2 and I just can not see how I can do this.

Table 2 needs to be cleaned up and organized better before I can link it to Table 1.

Not sure how.....

Sue
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top