Query/Table cleanup

[sue0912]

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

 

[jadams0173]

What version of SQL? if 2005 or greater there is a PIVOT function built in.

 

[sue0912]

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

 

[sue0912]

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

 

[sue0912]

or subquery it somehow....